Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DemoFour
Responsive Resident
Responsive Resident

Help with measure not showing all results

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. 

 

Table.png

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. 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
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

startab.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

9 REPLIES 9
PhilipTreacy
Super User
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

startab.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


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

firstrow.png

 

So looking at all the rows for Client 13209 the row highlighted in green is returned.

firstscore.png

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.

lastrow.png

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


@PhilipTreacy 

 

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. 

@DemoFour 

Glad to help 🙂



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


DemoFour
Responsive Resident
Responsive Resident

Hopefully this link works @PhilipTreacy 

PBIX 

DemoFour
Responsive Resident
Responsive Resident

@PhilipTreacy 

 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy
Super User
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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.