topic Re: Expression in DAX in Developer

Expression in DAX

Kavya123 — Mon, 29 Mar 2021 07:48:47 GMT

Hi Everyone,

We are converting report from Qlikview to Power BI. Please help me to write DAX expression for the same. I wrote the dax expression but I am not getting expected result.

Dax expression:

Measure 3 Quality & Innovation = CALCULATE(AVERAGE('PROFILERTABLENEWSUBMITTED Score'[NEWScore]), FILTER('PROFILERTABLENEWSUBMITTED Score', 'PROFILERTABLENEWSUBMITTED Score'[PDataPoint]=1209), FILTER('PROFILERTABLENEWSUBMITTED Score','PROFILERTABLENEWSUBMITTED Score'[Answer_Flag]=1))

Measure 4 Quality & Innovation = CALCULATE(AVERAGE('PROFILERTABLENEWSUBMITTED Score'[NEWScore]), FILTER('PROFILERTABLENEWSUBMITTED Score', 'PROFILERTABLENEWSUBMITTED Score'[PDataPoint]=1210), FILTER('PROFILERTABLENEWSUBMITTED Score','PROFILERTABLENEWSUBMITTED Score'[Answer_Flag]=1))

Measure 2 Quality & Innovation = FLOOR(CALCULATE(AVERAGE( 'PROFILERTABLENEWSUBMITTED Score'[NEWScore]) , 'PROFILERTABLENEWSUBMITTED Score'[PDataPoint]=1210, 'PROFILERTABLENEWSUBMITTED Score'[Answer_Flag]=1,ALL('PROFILERTABLENEWSUBMITTED Score'[POrganization])) ,1)

CALCULATE( COUNTROWS (

FILTER (

VALUES( 'PROFILERTABLENEWSUBMITTED Score'[POrganization]),

if ( and ([Measure 2 Quality & Innovation]>[Measure 4 Quality & Innovation], [Measure 3 Quality & Innovation]<[Measure 4 Quality & Innovation]), 1,0) )) , FILTER( 'PROFILERTABLENEWSUBMITTED Score', 'PROFILERTABLENEWSUBMITTED Score'[PDataPoint]=1209 || 'PROFILERTABLENEWSUBMITTED Score'[PDataPoint]=1210))

We are calculating the Count of POrganization WHERE the average score for PDataPoint 1210 is less than the total average (rounded down to nearest integer) AND the average score for PDataPoint 1210 is larger than the average score for PDataPoint 1209.

Re: Expression in DAX

lbendlin — Mon, 29 Mar 2021 20:34:19 GMT

Please provide sample data in usable format (not as a picture) and show the expected outcome.

Re: Expression in DAX

Anonymous — Thu, 01 Apr 2021 05:36:58 GMT

Hi @Kavya123 ,

You can create the measeures as below:

Avg score of 1209 = AVERAGEX ( FILTER ( ALLSELECTED ( 'PROFILERTABLENEWSUBMITTED Score' ), 'PROFILERTABLENEWSUBMITTED Score'[PDataPoint] = 1209 && 'PROFILERTABLENEWSUBMITTED Score'[Answer_Flag] = 1 ), 'PROFILERTABLENEWSUBMITTED Score'[NEWScore] )

Avg score of 1210 = AVERAGEX ( FILTER ( ALLSELECTED ( 'PROFILERTABLENEWSUBMITTED Score' ), 'PROFILERTABLENEWSUBMITTED Score'[PDataPoint] = 1210 && 'PROFILERTABLENEWSUBMITTED Score'[Answer_Flag] = 1 ), 'PROFILERTABLENEWSUBMITTED Score'[NEWScore] )

Avg score of Total = FLOOR ( AVERAGEX ( FILTER ( ALLSELECTED ( 'PROFILERTABLENEWSUBMITTED Score' ), 'PROFILERTABLENEWSUBMITTED Score'[Answer_Flag] = 1 ), 'PROFILERTABLENEWSUBMITTED Score'[NEWScore] ), 1 )

Measure = CALCULATE ( DISTINCTCOUNT ( 'PROFILERTABLENEWSUBMITTED Score'[POrganization] ), FILTER ( ALLSELECTED ( 'PROFILERTABLENEWSUBMITTED Score' ), [Avg score of 1210] < [Avg score of total] && [Avg score of 1210] > [Avg score of 1209] ) ) + 0

If the above one is not working, please provide some sample data (exclude sensitive data) and your expected result with sample or screenshots. Thank you.

Best Regards

Re: Expression in DAX

Anonymous — Mon, 12 Apr 2021 09:37:36 GMT

Hi @Kavya123 ,

Did you get the expected result base on my provided measure formulas? If yes, could you please mark it as Answered? It will help the others find the solution easily if they face the same problem with you. Thank you.

Best Regards