Forum Discussion

Kavya123's avatar
Kavya123
Helper III
5 years ago

Expression in DAX

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

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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