Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good morning,
I am working on a report where i need to find the first result and then the last result and what the diffierence in change is for each piece of work.
I can find the Min and Max using a measure (Star order is a calculated column that ranks the order the work was completed by date).
First Star =
CALCULATE(
MIN( 'Outcome Star'[Star Order] ),
ALLEXCEPT('Outcome Star' , 'Outcome Star'[QL Client Number]
)
)
I then throught that I can use this meassure to return the first score
First Star Score =
IF(
[First Star] ,
SELECTEDVALUE( 'Outcome Star'[Average Score] )
)
Then use these two measures to give the result with
Difference btw First & Last Star = [Last Star Score] - [First Star Score]
These seams to work, however it is only returning results for clients with only 1 completed score.
I thought this would be a good solution that I could then implement into fewer measures using variables to get the scores and then the last measure.
Any thoughts on why this is only working for clients with one row?
any other solutions greatfully recieved.
Solved! Go to Solution.
Hi @DemoFour
Download PBIX file with modifications
These measures needed modification to select the correct values
First Star Score = CALCULATE(MIN('Outcome Star'[Average Score]), FILTER('Outcome Star', 'Outcome Star'[QL Client Number] = SELECTEDVALUE( 'Outcome Star'[QL Client Number] ) && 'Outcome Star'[Star Order] = [First Star]))
Last Star Score = CALCULATE(MAX('Outcome Star'[Average Score]), FILTER('Outcome Star', 'Outcome Star'[QL Client Number] = SELECTEDVALUE( 'Outcome Star'[QL Client Number] ) && 'Outcome Star'[Star Order] = [Last Star]))
Giving this table
Regards
Phil
Proud to be a Super User!
Hi @DemoFour
Download PBIX file with modifications
These measures needed modification to select the correct values
First Star Score = CALCULATE(MIN('Outcome Star'[Average Score]), FILTER('Outcome Star', 'Outcome Star'[QL Client Number] = SELECTEDVALUE( 'Outcome Star'[QL Client Number] ) && 'Outcome Star'[Star Order] = [First Star]))
Last Star Score = CALCULATE(MAX('Outcome Star'[Average Score]), FILTER('Outcome Star', 'Outcome Star'[QL Client Number] = SELECTEDVALUE( 'Outcome Star'[QL Client Number] ) && 'Outcome Star'[Star Order] = [Last Star]))
Giving this table
Regards
Phil
Proud to be a Super User!
Hi @PhilipTreacy
Thanks for your post of the amended code.
So what I needed to do was put in a filter context to achieve getting all the results as I needed to still group the measure by Client number and Star Number.
So the problem was that the simple IF( statement was not getting both of the filter context's only the Star Order = 1
have a read this right?
Thank you for your time today.
Hi @DemoFour
Yes the [First Star Score] measure needed to find the MIN('Outcome Star'[Average Score] based on the filtered table defined by filtering for the current client 'Outcome Star'[QL Client Number] = SELECTEDVALUE( 'Outcome Star'[QL Client Number] ) AND where the Star Order equalled the First Star 'Outcome Star'[Star Order] = [First Star]
If you look at the FILTER statement on its own, it actually returns a single row - I'm using Client ID 13209 to replace the SELECTEDVALUE in this
So looking at all the rows for Client 13209 the row highlighted in green is returned.
The MIN('Outcome Star'[Average Score] is a bit misleading as there's only 1 number in the Average Score column in the row returned by FILTER, but you must provide some aggregation function to CALCULATE. You could just as well use MAX or SUM instead of MIN here.
Once you've isolated a single row it's easy to get the correct score.
Likewise for the [Last Star Score] the FILTER returns a single row where the Star Order value equals [Last Star] so you just pick out the Average Score in that row.
Regards
Phil
Proud to be a Super User!
Thank you for that, I did not know that you could work with "cells" in the table this way. That's a really good explanation and has joined many dots for me.
Thank you for taking the time to set that out, this forum is really a great place to not only find answers but learn and understand DAX in a less prosaic way.
Glad to help 🙂
Proud to be a Super User!
I am just having some issues supplying the data into a table for you as I can't post a PBIX file and I get an error on my post!
Hi @DemoFour
Upload the file to OneDrive or Dropbox then post the link to it in here.
regards
Phil
Proud to be a Super User!
Hi @DemoFour
Can you please supply your PBIX file. You are referring to columns and measures in your code (above) that I don't see any data for and don't know how they are calculated.
regards
Phil
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |