Forum Discussion
Helper ICalculate with measures
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] )
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") )
3 Replies
- amitchandak
Super User
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") )
edcdcpowerbiThankyou, 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!
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.- values( 'Client'[ID]) - gives us a list of unique values from the column 'Client'[ID]
