Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mann
Resolver III
Resolver III

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

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:

Table1.PNG

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

Action1.PNG

(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".

Action2.PNG

Please advice how this can be done.

1 ACCEPTED SOLUTION
TeigeGao
Solution Sage
Solution Sage

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:

111.png222.png

Best Regards,

Teige

View solution in original post

2 REPLIES 2
TeigeGao
Solution Sage
Solution Sage

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:

111.png222.png

Best Regards,

Teige

Thanks TeigeGao!!
It worked as expected.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.