Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

LOD to DAX conversion

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.

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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])
      ))
      • Icey's avatar
        Icey
        Icon for Community Support rankCommunity Support
        4 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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