Finding Max of a column value as per groups dynamically when user selects Slicer on DateTime

[Mann]

Hi All,

I have one interesting scenario and I am kind of struggling to find a fix for it.

There is a table which has "Parent", "Child", "DateTime", "Value" field as shown:

I need to do two main things:

(1) I have to find maximum of value column which will be displayed as another column in above table visual. This maximum value should be calculated as per user selected "DateTime" slicer. For e.g. as shown below when "3/20/2019 6:00:00AM" value is selected, New column "MaxValue" should show "50" for first three rows (as per group of Parent = 1 and ignoring child values (a/b/c) ) and "15" for fourth row (as per group of Parent = 2 and ignoring child values (b))

(2) If I select two or more no. of dates then this measure should find maximum as per parent group and should ignore child selections and Date time also as shown: For the first six rows "MaxValue" column in this table visual should show "70" and last two rows should show "40".

Please advice how this can be done.

[TeigeGao · Accepted solution]

Hi Mann,

According to your description, my understanding is that you want to find the max data for each group of parent based on the selected DateTime.

In this scenario, we can use the following measure:

MaxValue = CALCULATE(MAX(Data[Value]),FILTER(ALLSELECTED(Data),Data[Parent] = MIN(Data[Parent])))

The result will like below:

Best Regards,

Teige

[Mann]

Thanks TeigeGao!!
It worked as expected.