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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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