Need help for dax function to show the current score and performance

[xiumi_hou]

Dear all,

I have a table like below: 

Name	Sesison Date	Question	Score
People 1	7/1/2018	Q1	2
People 1	7/1/2018	Q2	3
People 1	7/1/2018	Q3	1
People 1	7/1/2018	Q4	2
People 1	8/1/2018	Q1	2
People 1	8/1/2018	Q2	1
People 1	8/1/2018	Q3	3
People 1	8/1/2018	Q4	1
People 1	8/20/2019	Q1	2
People 1	8/20/2019	Q2	4
People 1	8/20/2019	Q3	2
People 1	8/20/2019	Q4	1
People 2
People 2
People 2
People 2
People 2
People 2
People 2

Ex. When I filter people 1, I want to show the last session date score and the comparison with the previous score.

  In this case, the last session date is 2019-08-20, so my ideal result is :

  

The first column is the last session score, then follow by a emoji to indicate it's improve/no change/worsen, and follow by a sentence to indicate the performance. 

If the question score is higher then previous session, then it worsen, if lower, then improved.

Thank you so much. Below is the pdix file. If you can help me any part of the calculation, that would be appreciate it. Thanks!

https://1drv.ms/u/s!ArC4uvMBwvXvgQQbeh6EEtv3ZVSP?e=Zb7Oz1

https://1drv.ms/x/s!ArC4uvMBwvXvgQLM3Sy_GYY8WPpn?e=OH2WM8

[v-diye-msft]

Hi @xiumi_hou 

Let me know if you'd like to get below results:

1. Use below measure to create the max session date : 

Measure = var a = CALCULATE(MAX('Table'[Sesison Date]),ALL('Table'),VALUES('Table'[Name]))
Return
IF(a=MAX('Table'[Sesison Date]),MAX('Table'[Score]),BLANK())

Filter the measure is not blank:

 

2. Create the measure2 to generate the score in last session date:

Measure 2 = var a = CALCULATE(MAX('Table'[Sesison Date]),FILTER(ALL('Table'),[Sesison Date]<MAX('Table'[Sesison Date])))
Return
CALCULATE(MAX('Table'[Score]),FILTER(ALL('Table'),[Sesison Date]=a),VALUES('Table'[Question]))

3. Then do the comparison using if function:

Measure 3 = IF([Measure]=[Measure 2],"No Change",IF([Measure]>[Measure 2],"Worsen",IF([Measure]<[Measure 2],"Improved")))

[xiumi_hou]

I have attached the data document. Can you help me do that in the attached pdix file? I have some problems. Thank you ! @v-diye-msft 

[v-diye-msft]

Hi @xiumi_hou 

Could you please confirm the pbix you attached is the right one? I didn't see the question column as you indicated in the previous post. Or you'd like to use the Quarter rather than Question? 

[xiumi_hou]

@v-diye-msft  Hi, diye, below is the link for the file

https://1drv.ms/u/s!ArC4uvMBwvXvgQiB1c1IiFqObNlM?e=BFOKN3

https://1drv.ms/x/s!ArC4uvMBwvXvgQYtYiz-rgO5FCYq?e=yvGDlH

By the way, if I only want to show the last two columns (score and comparision) or the last column(comparision) by question. Do you have an idea to do that？ Thanks.

[v-diye-msft]

Hi @xiumi_hou 

Working on it. 

[xiumi_hou]

Thank you so much!

[v-diye-msft · Accepted solution]

Hi @xiumi_hou 

There too many rows in your data, that takes much long time to run the measures, please kindly check the attached pbix whether meet your requirement.

[xiumi_hou]

@v-diye-msft  Thanks, I will attached a new one shortly. 

[xiumi_hou]

@v-diye-msft  Thank you so much for your help. Yes, I would like to show the max session score and comparision column only. But when I tried to create dax fucntion like this, it gave me the wrong last session date score result. Can you help me look at it? Thanks!

[bchager6]

I might be able to help you chip away at the code you need to write. 

Here are steps to retrieve the most recent session date:

1. Create a measure to determine the “MaxDate”
1. MaxDate = CALCULATE(MAX('Sheet1'[Session Date]), ALL('Sheet1'))
2. Create a calculated column for “Is Latest”
1. Is Latest = IF('Sheet1'[Session Date] = 'Sheet1'[MaxDate], "Latest","")
   

"Latest" should always be the most recent date. As far as retrieving the score for the previous date; I believe you're going to need to figure out how to store that in a variable (VAR), or use the EARLIER function.