cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mann Member
Member

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

Accepted Solutions
Community Support Team
Community Support Team

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

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
Community Support Team
Community Support Team

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

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

Mann Member
Member

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

Thanks TeigeGao!!
It worked as expected.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 425 members 4,647 guests
Please welcome our newest community members: