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