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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Didik62
Frequent Visitor

Asking : How to showing selected row data (some column) based based on dynamic max value

I am looking for how to visualize the table for entire data with max value as reference and following the selected filter (by slicer)

Here my data

 

Area codeProduct CodeAgeQty
AProduct 1512
AProduct 126
BProduct 1754
CProduct 11232
DProduct 192
AProduct 238
BProduct 2219
BProduct 22111
CProduct 21120
BProduct 384

 

I need to find which product that has oldest age, how many the qty and how many the total qty of its product based on how i filter the area (using slicer)

 

This is how table need to be visualized on PBIX layout

Filter/slicer all

Product CodeMax AgeQty of Max AgeTotal Qty of product
Product 11232106
Product 2212048
Product 3844

 

Filter/slicer for A only

Product CodeMax AgeQty of Max AgeTotal Qty of product
Product 151218
Product 2388
  

 

 

 

Filter/slicer for B only

Product CodeMax AgeQty of Max AgeTotal Qty of product
Product 175454
Product 2212020
Product 3844

 

Thank you

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

Hi @Didik62 

I think you can try Max, Sum function in value field in table visual and create a measure to achieve your goal.

Max of Age column is Age column from table calculated by Maximum function in table visual.

1.png

Total Qty of Product column is Qty column from table calculated by Sum function in table visual.

2.png

Qty of Max Age is as measure.

Qty of Max Age = 
CALCULATE(SUM('Table'[Qty]),FILTER(ALL('Table'),'Table'[Product Code] = MAX('Table'[Product Code])&&'Table'[Age] = MAX('Table'[Age])))

Create a slicer by Area code column from table. Result is as below.

All:

1.png

Select A:

2.png

Select B:

3.png

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

Hi @Didik62 

I think you can try Max, Sum function in value field in table visual and create a measure to achieve your goal.

Max of Age column is Age column from table calculated by Maximum function in table visual.

1.png

Total Qty of Product column is Qty column from table calculated by Sum function in table visual.

2.png

Qty of Max Age is as measure.

Qty of Max Age = 
CALCULATE(SUM('Table'[Qty]),FILTER(ALL('Table'),'Table'[Product Code] = MAX('Table'[Product Code])&&'Table'[Age] = MAX('Table'[Age])))

Create a slicer by Area code column from table. Result is as below.

All:

1.png

Select A:

2.png

Select B:

3.png

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi Rico. for this solution i have some problem when same product code from different area have the same Age.
How i could select the correct data after filtering by area

 

New data

Area codeProduct CodeAgeQty
AProduct 11212
AProduct 126
BProduct 11254
CProduct 11232
DProduct 192
AProduct 238
BProduct 2219
BProduct 22111
CProduct 21120
BProduct 384

 

Filter all for Product 1

Product CodeMax AgeQty of Max AgeTotal Qty of product
Product 11298106

 

Filter for area A

Product CodeMax AgeQty of Max AgeTotal Qty of product
Product 1121218

 

Filter for area B

Product CodeMax AgeQty of Max AgeTotal Qty of product
Product 1125454

 

Would you help me once more.

 

Thank you

Thank you so much. it works for me.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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