AX 2012 & Power BI

I'll mention about eaiest way of learn Power BI, some troubles and important points about using AX data with Power BI at this page:

-Best source which I found about Power BI is a course at  EDX named  Analyzing and Visualizing Data with Power BI. This a video & subtitle combined course with an excellent way. When video is streaming subtitle is moving and you can click any part of subtitle and video jumps that part. There are some tests and nothing skips; Year-to-Day, gateway etc...

-You need Date table for carry forward and Waterfall report. I used a script which I found from Power BI İstanbul or you can use SQLBI team's solution.

let fnTarihTablosu = (BaşlangıçTarihi as date, BitişTarihi as date, MaliYılBaşlamaAyı as number) as table =>

  let

    DayCount = Duration.Days(Duration.From(BitişTarihi - BaşlangıçTarihi)) + 1,

    Source = List.Dates(BaşlangıçTarihi,DayCount,#duration(1,0,0,0)),

    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),

    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),

    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Tarih"}}),

    YılEkle = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Tarih]),type text),

    CeyrekEkle = Table.AddColumn(YılEkle, "Quarter No", each Date.QuarterOfYear([Tarih])),

    AyEkle = Table.AddColumn(CeyrekEkle, "Month No", each Date.Month([Tarih]), type text),

    GünEkle = Table.AddColumn(AyEkle, "Day of Month", each Date.Day([Tarih])),

    AyEkleIsim = Table.AddColumn(GünEkle, "Month", each Date.ToText([Tarih], "MMMM"), type text),

    AyEkleKısaIsim = Table.AddColumn(AyEkleIsim, "Month Short", each Date.ToText([Tarih], "MMM"), type text),

    HaftanınGünüEkle = Table.AddColumn(AyEkleKısaIsim, "Day of Week No", each Date.DayOfWeek([Tarih])+1),

    GünIsmiEkle = Table.AddColumn(HaftanınGünüEkle, "Day Of Week", each Date.ToText([Tarih], "dddd"), type text),

    HaftaNoEkle= Table.AddColumn(GünIsmiEkle, "Week No", each Date.WeekOfYear([Tarih])),

    ChangedType1 = Table.TransformColumnTypes(HaftaNoEkle,{{"Week No", Int64.Type},{"Year", type text},{"Day Of Month", Int64.Type}, {"Month No", Int64.Type}, {"Quarter No", Int64.Type}, {"Week of Day No", Int64.Type}}),

    KısaYılEkle = Table.AddColumn(ChangedType1, "Year Short", each Text.End(Text.From([Yıl]), 2), type text),

    MaliYıl = Table.AddColumn(KısaYılEkle, "Fiscal Year", each "MY"&(if [Ay No]>=MaliYılBaşlamaAyı then Text.From(Number.From([Yıl Kısa])+1) else [Yıl Kısa]))

in

    MaliYıl

in

    fnTarihTablosu

-There is a common problem with Turkish about "i" and "ı" letter. I faced with  DataAreaId. DataAreadId at Company table was "DEIM" and at others was "deim" and Power BI cannot match these with Turkish collate. Also primary/secondary address and expired address issues would be trouble about duplicate key issue. There are some tweaks need about these:

select c.accountnum,d.name,c.custgroup,l.state,l.COUNTRYREGIONID,lc.SHORTNAME,  iif(c.dataareaID = 'deim','DEIM',c.dataareaid) as DATAAREAID

from custtable c left join DIRPARTYTABLE d on c.PARTY = d.RECID

left join DIRPARTYLOCATION dl on d.RECID = dl.PARTY

 left join LOGISTICSPOSTALADDRESS l on  dl.LOCATION = l.LOCATION

left join LOGISTICSADDRESSCOUNTRYREGIONTRANSLATION lc on lc.COUNTRYREGIONID = l.COUNTRYREGIONID

where

 

lc.LANGUAGEID = 'EN-AU'

and Dl.ISPRIMARY = 1 and L.VALIDTO IN (select top 1 VALIDTO from LOGISTICSPOSTALADDRESS a 

where a.LOCATION = dl.LOCATION order by VALIDTO desc)

First highlighted command part at upper about Turkish collate problem, second for avoid duplicated records about different languages, third about get valid primary address.

-For periodic data transfer to cloud you have to install a gateway ( preferably at SQL server. There is a lesson about gateway at EDX.

-Don't forget customize your reports with Phone View button at Power BI desktop. This's easy but if you missed up this step your reports won't be very impressive at phone.

-Power BI can create a relation with one-by-one field. You can fix this issue by use merged column feature of Power BI. There is a lesson about merged columns at that EDX course.

-A lot of primary key value duplicates by different DataArea's at AX. This'll a trouble about  1:N relations. You can handle this too with method at upper. Sample for CustTable use AccountNum & DataAreaId and for normalized invoice table use CustAccount & DataAreaId...