topic Re: LOD to DAX conversion in DAX Commands and Tips

LOD to DAX conversion

Anonymous — Sun, 19 Jun 2022 15:18:41 GMT

Hi Guys,

I hope you all are doing well.

I am working on a Tableau to Power Bi migration project and stuck in yet another calculation.

I want to convert one LOD to Dax in power BI. Below the the Tableau LOD calculation:

{ FIXED [Parts Id]:sum({ FIXED [Parts Id],[Part Number],[Country]: AVG(if [Year] = int([ParamYear])-1 then [Vol] end )})}

I tried to use below Calculation in Power BI

Var yr= SELECTEDVALUE(CC[Param_year]) return

SUMX(

SUMMARIZE(CC,CC[parts id],CC[part number], cc[country],

"volpyref", AVERAGEX(cc,if(CC[param_year]=yr-1, CC[vol]))),

[volpyref])

The similar calculation is working perfectly when I am using it for [Year] = int([ParamYear]) . I took the variable and passed the same in the Dax, please refer above for more details .

But when its [Year] = int([ParamYear])-1 , it is giving me blank everytime in power BI but there are values showing for tableau..

I tried several methods but i am getting the same everytime. I am trying to calculate this to find previous year vol.

**And year filter is being mandatorily used.

Re: LOD to DAX conversion

amitchandak — Mon, 20 Jun 2022 11:34:46 GMT

Anonymous , try a measure like

calculate(Average(Table[Vol]), filter(Table, Table[Year] = selectedvalue(Year[Year]), allexcept(Table,Table[Part ID], [Part Number], [Country])))

Allexcept with fact table and dimension

LOD- FIXED (Level of Details): https://youtu.be/hU-cVOwDCvY

Re: LOD to DAX conversion

Anonymous — Mon, 20 Jun 2022 12:30:30 GMT

Hi Amit, thanks for your reply,

I tried your calculation and many other ways, but the values are not matching.

var yrs= SELECTEDVALUE(CC[param_year]) return

var yr = CONVERT(yrs,INTEGER) return

sumx( CC,

CALCULATE(AVERAGEx(cc,CC[vol])

,CONVERT(cc[param_year], INTEGER)= (yr-1),ALLEXCEPT(cc,CC[parts_id],CC[country],CC[part_number])

))

Re: LOD to DAX conversion

Icey — Mon, 27 Jun 2022 09:34:38 GMT

Hi Anonymous ,

Try this:

Measure = VAR yr = SELECTEDVALUE ( CC[Param_year] ) RETURN SUMX ( SUMMARIZE ( CC, CC[parts id], CC[part number], cc[country], "volpyref", CALCULATE ( AVERAGE ( CC[vol] ), FILTER ( ALL ( CC ), CC[param_year] = yr - 1 ) ) ), [volpyref] )

If this doesn't work, share some sample data for test.

Reference: How to Get Your Question Answered Quickly - Microsoft Power BI Community

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: LOD to DAX conversion

Anonymous — Tue, 05 Jul 2022 10:29:09 GMT

Hi Icey,

Apologies for the late reply as i was busy with some other stuffs.

Coming to your provided solution . Atleast this time I am not getting blank, however the value isn't matching to Tableau version.

I will try to provide you sample data after sometime if its possible.

Thanks

Re: LOD to DAX conversion

Anonymous — Wed, 06 Jul 2022 09:07:24 GMT

Scenario1: I havent selected any year from the year slicer and now PY year is coming for me, also I have hardcoded the previous year as 2021.

*******

Scenario2:

When I have selected an year from the Base year slicer, PY value will vanish either using hardcode or using a variable also. I have also included your solution, but its coming unique value for all. But i should get the PY year value as show with the hard code value

You may also see this coming when i remove country from the table, as i dont need to show it.

I will anyway be using country filter for my main dashboard.

Please suggest me what needs to be done here. I don't think this filter(all(table_name) is required here. but when I am removing this . I am getting blank here as well