topic Re: Calculate with measures in DAX Commands and Tips

Calculate with measures

edcdcpowerbi — Thu, 10 Feb 2022 09:16:22 GMT

I am trying to create a measure which returns all 'ClientIDs' with a higher final score.

I have Two measures 'Initial Score' & 'Final Score, the DAX for them is below

CALCULATE(SUM('Client')'[Total Score]),'Client[Stage] = "Final")

CALCULATE(SUM('Client')'[Total Score]),'Client[Stage] = "Initial")

I now wish to create measure the third measure which return all the 'ClientIDs' that have a higher final score.

A bit lost on how to go about it, this is what i currently have which does not work.

Would appreciate any help and advice

CALCULATE( COUNT( 'Client'[ID]) , [Final Score] > [Initial Score] )

Re: Calculate with measures

amitchandak — Thu, 10 Feb 2022 09:32:41 GMT

edcdcpowerbi ,

Try like

CALCULATE( COUNTX(filter(values( 'Client'[ID]) , [Final Score] > [Initial Score] ), [Client ID] ))

if needed add a filter in these two too

CALCULATE(SUM('Client')'[Total Score]),filter('Client , 'Client[Stage] = "Final"))

CALCULATE(SUM('Client')'[Total Score]),filter('Client , 'Client[Stage] = "Initial") )

Re: Calculate with measures

edcdcpowerbi — Thu, 10 Feb 2022 10:05:32 GMT

Thankyou, that works perfectly, are you ok to give a breakdown as to what is happening in the measure, I'm new to DAX so would love to understand more!

Re: Calculate with measures

Anonymous — Thu, 10 Feb 2022 11:08:03 GMT

We have basically three functions at work here. Separated by colour.

COUNTX(filter(values( 'Client'[ID]) , [Final Score] > [Initial Score] ), [Client ID] ))

Let's break it down from the innermost arguments:

values( 'Client'[ID]) - gives us a list of unique values from the column 'Client'[ID]
filter(values( 'Client'[ID]) , [Final Score] > [Initial Score] ) - Now that we have a list of unique values of 'Client'[ID], we filtered these values where the condition '[Final Score] > [Initial Score]' holds true using the filter() function.
COUNTX(filter(values( 'Client'[ID]) , [Final Score] > [Initial Score] ), [Client ID] )) - This function simply gives the count of the filtered values of 'Client'[ID] received from the filter() function mentioned above.

In this we we find the count of 'Client'[ID]) where [Final Score] > [Initial Score].

In case you need to understand in detail each of these functions you can use dax.guide
Hope this helps.