Forum Discussion

edcdcpowerbi's avatar
edcdcpowerbi
Icon for Helper I rankHelper I
4 years ago
Solved

Calculate with measures

  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('Clie...
CALCULATE
DAX
  • amitchandak's avatar
    amitchandak
    4 years ago

    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's avatar
edcdcpowerbi
Icon for Helper I rankHelper I
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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.