Calculate with measures

[edcdcpowerbi]

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] )

[amitchandak · Accepted solution]

@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") )

[edcdcpowerbi]

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!

[Anonymous]

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.