Need Help in Converting formula of Tableau to Power Bi

[Aditya78]

Hey all, please help me in converting this Tableau formula to DAX in PowerBi.

{ FIXED [research number]:sum({ FIXED [research number],[department_center]:MAX([department_personnel])})}

Help will be highly appreciated.

Thanks

[Anonymous · Accepted solution]

HI @Aditya78,

According to your description and tabular expression, It seems like a common two-level aggregation expression. (sum of the max value based on correspond group fields)
For this scenario, you can try to use the following measure formula if helps:

Measure =
VAR currNumber =
    VALUES ( 'Table'[research number] )
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        [research number],
        [department_center],
        "max_DP", MAX ( 'Table'[department_personnel] )
    )
RETURN
    SUMX ( FILTER ( summary, [research number] IN currNumber ), [max_DP] )

Regards,
Xiaoxin Sheng

[Aditya78]

@amitchandak ,

Thanks for your kind response. But how can this work as in my mention formula. There are two expressions as well which are sum and max.

[amitchandak]

@Aditya78 , Try like

calculate([research number], allexcept(Table,[research number], [department_personnel]))