Forum Discussion
Select an specific value from a filtered table
Hello, dear PBI experts.
I have a table that contains data related to "Division", "Date" and "Number". I have created two slicers to filter by Division and Date. After these filters are applied I would like to show in a card the value related to the latest date. Could you kindly advise how to achieve this?
In the example below, I've filtered "A" until 20/13/2023 and I would like to show 0.87 as a result... and If I filter "A" until 11/04/2023, I would like to show 0.92... you get the idea, right?
Thanks for your help!
Pat.
Hey Patricio479 ,
if this is your target result:you can try to apply this formula into your model. Should work for your described purpose.
MAX Value = CALCULATE( MAX(yout_table[Number]), 'Calendar'[Date] = MAX(your_table[Date]) )
RegardsPatricio479 Ok, let's test it, if it doesn't work then I will recreate dataset on my end to test it:
Measure = CALCULATE ( MAX ( Table[Number] ), INDEX ( 1, SUMMARIZE ( ALLSELECTED ( Table ), Table[Date], Table[Division] ), ORDERBY ( Table[Date], DESC ), PARTITIONBY ( Table[Division] ) ) )
12 Replies
- parry2k
Super User
Patricio479 will you always select one division, what happens if two divisions are selected or that is never going to be the case?
Hello Parry2k,
I think that - for the time being - I will just constrain the filter to work for just one division (single selection). Is it simplier this way? 🙂
A multiselection might come in the future...if that is the case, I will open another post when the time comes...Thanks!
sergej_ogHey Patricio479 ,
if this is your target result:you can try to apply this formula into your model. Should work for your described purpose.
MAX Value = CALCULATE( MAX(yout_table[Number]), 'Calendar'[Date] = MAX(your_table[Date]) )
RegardsHello Sergej!
Thanks for your help! The suggested formula worked for the intended purpose.
Have a good one!
Pat.
- parry2k
Patricio479 you can use the new INDEX function to get it:
Max Number = CALCULATE ( MAX ( Table[Number] ), INDEX ( 1, ALLSELECTED ( Table ), ORDERBY ( Table[Date], DESC ), PARTITIONBY ( Table[Division ) ) )Advantage here is if you have multiple Division selected and you view it by Divison you will max value of each division. Killing 2 birds with one stone.
Hello Parry2k,
Thanks for your fast reply. I tried to input your formula, but I am getting the following error:
Thanks for your help once more!
Pat.
- Ashish_Mathur
Hi,
Share data in a format that can be pasted in an MS Excel file.
Hello Ashish!
Thanks for your message. Hope this helps:Division Date Number A 15/01/2023 0,82 B 15/01/2023 0,94 C 15/01/2023 0,88 D 15/01/2023 0,86 A 25/02/2023 0,98 B 25/02/2023 1,14 C 25/02/2023 0,98 D 25/02/2023 1,15 A 20/03/2023 0,87 B 20/03/2023 1,52 C 20/03/2023 1,07 D 20/03/2023 1,60 A 11/04/2023 0,92 B 11/04/2023 1,52 C 11/04/2023 1,07 D 11/04/2023 1,60 Thanks!
Pat.
- parry2k
Add one more line after partitionby
,MATCHBY(Table[Division])
Hello Parry2k,
I still get an error. This time:
This is still showing an error in the "partitionby" line.
Thanks for your help,Pat.
- parry2k
Patricio479 Ok, let's test it, if it doesn't work then I will recreate dataset on my end to test it:
Measure = CALCULATE ( MAX ( Table[Number] ), INDEX ( 1, SUMMARIZE ( ALLSELECTED ( Table ), Table[Date], Table[Division] ), ORDERBY ( Table[Date], DESC ), PARTITIONBY ( Table[Division] ) ) )Hello Parry2k,
Your solution has worked!
Thanks for your time, your help was much appreciatted!
Cheers,
Pat.
