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 t...
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

      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