Only show dataset result based on last date score

[Anonymous]

I am trying to have a Power BI report page that shows the number of Premises where they have a score of over 12. The issue is, a premise could have been scored 12 in a job in 2015, but then in 2018 may have been downgraded to a score of 10. I am therefore not interested in this premise as going by it's last date of inspection it is lower than 12. However, because the premise has a score of 12 attached to it, if when i filter down to just 12+ scores on the report page, the premise is obviously shown with the 2015 inspoection date shown. This is not what we want as it's newer inspection has a lower score. I want to ignore these premises if they have a newer inspection date with a lower score and therefore only allow premises to show on the count/ data table if their most recent date of inspection resulted in a score of over 12, regardless of what their previous scores were.

e.g. for the below premise "12345" I would not want to see this premise show if my slicer had 12+ score selected as its most recent date didnlt achieve this score.

PremiseID	Date	Score
12345	01/01/2015	12
12345	10/10/2018	10

Hope this makes sense.

I have attempted to use max and last date functions ans grouping on the PremiseID but haven't had much success.

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Try to create measures as following:

_lastScore = 
CALCULATE(MAX('Table'[Score]),FILTER(ALL('Table'),'Table'[PremiseID]=MAX('Table'[PremiseID])&&'Table'[Date]=MAXX(FILTER(ALL('Table'),'Table'[PremiseID]=MAX('Table'[PremiseID])),[Date])))

_isOver = 
IF([_lastScore]>=[Score Value],1,0)

_isOver_OnlyLastDate = 
IF([_lastScore]>=[Score Value]&&SELECTEDVALUE('Table'[Score])>=[Score Value],1,0)

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-angzheng-msft Hi, thanks for the reply. This seems to do exactly what I needed it to do. The only thing is it is very very slow. Testing it out with a couple of premise ID's and only a couple columns it worked Ok, but since more data and more columns are added it is slow to the point it barely works. Any advice to speed it up? Thanks

[v-angzheng-msft]

Hi, @Anonymous 

Try to optimize the DAX query as follows:

_lastScore = 
//  1
// CALCULATE(MAX('Table'[Score]),FILTER(ALL('Table'),'Table'[PremiseID]=MAX('Table'[PremiseID])&&'Table'[Date]=MAXX(FILTER(ALL('Table'),'Table'[PremiseID]=MAX('Table'[PremiseID])),[Date])))
//  2
// VAR _MAXDATE=MAXX(FILTER(ALL('Table'),'Table'[PremiseID]=MAX('Table'[PremiseID])),[Date])
// VAR _LASTSCORE=
// CALCULATE(MAX('Table'[Score]),FILTER(ALL('Table'),'Table'[PremiseID]=MAX('Table'[PremiseID])&&'Table'[Date]=_MAXDATE))
//  3
// VAR _MAXDATE=MAXX(FILTER(ALL('Table'),'Table'[PremiseID]=MAX('Table'[PremiseID])),[Date])
// VAR _CURRENT_ID=MAX('Table'[PremiseID])
// VAR _LASTSCORE=
// CALCULATE(MAX('Table'[Score]),FILTER(ALL('Table'),'Table'[PremiseID]=_CURRENT_ID&&'Table'[Date]=_MAXDATE))
//  4
VAR _MAXDATE=MAXX(FILTER(ALL('Table'),'Table'[PremiseID]=MAX('Table'[PremiseID])),[Date])
VAR _CURRENT_ID=MAX('Table'[PremiseID])
VAR _LASTSCORE=
CALCULATE(MAX('Table'[Score]),KEEPFILTERS('Table'[PremiseID]=_CURRENT_ID&&'Table'[Date]=_MAXDATE))

RETURN _LASTSCORE

 Now, you just need  [_lastScore] and [_isOver]  2 measures.
Performance analysis results obtained through the performance analyzer:

Result:

This is the optimization that can be done in the DAX query part. If you want to get the best performance, you may need to adjust the model, etc. I attach the relevant information on the performance optimization below for your reference.
https://maqsoftware.com/insights/dax-best-practices
https://www.sqlbi.com/tv/dax-studio-2-7-improved-debug-experience/

https://www.sqlbi.com/tv/power-bi-dax-optimization-examples/

https://www.sqlbi.com/tv/my-power-bi-report-is-slow-what-should-i-do/

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-angzheng-msft . Unfortunately, when creating the measure and adding it to a data table, I get the following error. Any advice?

[v-angzheng-msft]

Hi, @Anonymous 
Please consider sharing more details, at least the measure formula? The information provided is too limited.
As you can see, the above formula worked well in my sample, and if the previous one worked for you, then I think the later one will work as well.

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-angzheng-msft Please see below the measure formula. I think the issue may be that my date is in a seperate table to the premID and the score? Thanks

_lastScore2 =

// 1

//CALCULATE(MAX('Table'[Score]),FILTER(ALL('Table'),'Table'[PremiseID]=MAX('Table'[PremiseID])&&'Table'[Date]=MAXX(FILTER(ALL('Table'),'Table'[PremiseID]=MAX('Table'[PremiseID])),[Date])))

// 2

// VAR _MAXDATE=MAXX(FILTER(ALL('Table'),'Table'[PremiseID]=MAX('Table'[PremiseID])),[Date])

// VAR _LASTSCORE=

// CALCULATE(MAX('Table'[Score]),FILTER(ALL('Table'),'Table'[PremiseID]=MAX('Table'[PremiseID])&&'Table'[Date]=_MAXDATE))

// 3

// VAR _MAXDATE=MAXX(FILTER(ALL('Table'),'Table'[PremiseID]=MAX('Table'[PremiseID])),[Date])

// VAR _CURRENT_ID=MAX('Table'[PremiseID])

// VAR _LASTSCORE=

// CALCULATE(MAX('Table'[Score]),FILTER(ALL('Table'),'Table'[PremiseID]=_CURRENT_ID&&'Table'[Date]=_MAXDATE))

// 4

VAR _MAXDATE=MAXX(FILTER(ALL('FactFSA'),'FactFSA'[PREM ID]=MAX('FactFSA'[PREM ID])),[SSRI Completion Date])

VAR _CURRENT_ID=MAX('FactFSA'[PREM ID])

VAR _LASTSCORE=

CALCULATE(MAX('FactFSA'[Poris Score]),KEEPFILTERS('FactFSA'[PREM ID]=_CURRENT_ID&&'SSRI Completion Date'[Date]=_MAXDATE))

RETURN _LASTSCORE

[v-angzheng-msft]

Hi, @Anonymous 

I noticed that in the LASTSCORE2 measure, you refer to fields from two tables, you don't mention that you need to refer to multiple tables. Unlike the formula I provided, my formula only references one table field. Or you still use the formula I provided earlier i.e. Comment 1 part, as you said, it works for you. You can also try to see if the code from comments 2 and 3 work.

You can consider sharing dummy sample files and expected output. Without sample files, such support work is inefficient. As you can see, your question doesn't seem to be difficult to implement, so many replies still retain the problem, which is too inefficient to solve the problem.
I will no longer be able to continue replying to this thread without sufficient information, please refer to the code I have provided.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AntonioHR]

Hello @Anonymous , 

If you just have 1 score per date and id you could try with a measure like this:

Measure = CALCULATE(SUM(Tabla[Score]);LASTDATE(Tabla[Date]))

Then in a table put PremiseID, Date (summary last date), Measure.

[Anonymous]

Hi @AntonioHR . Thanks for the reply. The measure doesn't like the ";" before the last date statement, so used a comma. But this doesnt work unfortunately. 

[AntonioHR]

Try a comma instead

[Anonymous]

Hi @AntonioHR , i did this and it allowed the measure to work. But it just brings back a blank score in the data table.

[AntonioHR]

mm I tested it with this data and worked. If I put in a visual PremiseID (no summary), Date ( summary = last date, no heriarchy) and the measure I get this: 

[amitchandak]

@Anonymous , try a measure like

Measure =
VAR __id = MAX ('Table'[PremiseID] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[PremiseID] = __id )
CALCULATE ( Sum ('Table'[Score] ), VALUES ('Table'[PremiseID] ),'Table'[PremiseID] = __id,'Table'[Date] = __date )

[Anonymous]

Hi @amitchandak  thanks for the reply. Unfortunately this brings back a blank result in the data table.

[amitchandak]

@Anonymous , Return was missing

Measure = 
VAR __id = MAX ('Table'[PremiseID] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[PremiseID] = __id ) 
return
CALCULATE ( Sum ('Table'[Score] ), VALUES ('Table'[PremiseID] ),'Table'[PremiseID] = __id,'Table'[Date] = __date )

with the latest date