Select an specific value from a filtered table

[Patricio479]

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.

 

[sergej_og · Accepted solution]

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])
        )

Regards

[parry2k · Accepted solution]

@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] )
    )
)

[Patricio479]

Hello Parry2k,

Your solution has worked!
Thanks for your time, your help was much appreciatted!

Cheers,
Pat.

[parry2k]

Add one more line after partitionby 

,MATCHBY(Table[Division])

[Patricio479]

Hello Parry2k,

I still get an error. This time:

This is still showing an error in the "partitionby" line.
Thanks for your help,

Pat.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.

[Patricio479]

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]

@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.

[Patricio479]

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.

[sergej_og · Accepted solution]

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])
        )

Regards

[Patricio479]

Hello Sergej!

Thanks for your help! The suggested formula worked for the intended purpose.

Have a good one!

Pat.

[parry2k]

@Patricio479 will you always select one division, what happens if two divisions are selected or that is never going to be the case?

[Patricio479]

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!