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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Jtbonner1986
Helper I
Helper I

Filtering and grouping by bucket

Hi All,

Not sure what to call this so apologies for the titles

 

I have a materials Table with the number of months cover that material has. inside the materials table is a column identfying the number of 'Months Cover' a material has. Inside the same table are 3 action columns; '1M action', '3M actions' and '6M action'.

 

I want to create a table visual which interacts with a 'Months Cover Period' filter to map a material with the months cover and the 'correct' action 

 

My Material Table

 

MaterialMonths Cover1M Action3M Action6M Action
1231TidyCleanNothing
4563CleanTidyNothing
7896NothingNothingNothing

 

So I want to create a filter than shows 1M 3M and 6M and when selected includes all rows where the material has = or under the selection; EG when Months Cover 3M is selected, it includes Material 123 & 456 and returns the action associated with the 3M action 

 

EG:

Months Cover 3M selected:

 

returned visual:

 

MaterialSelected Action
123Clean
456Tidy

 

Any help is so grateful 

 

Cheers

Josh

1 ACCEPTED SOLUTION

Hi @Jtbonner1986 

 

Sorry for misunderstanding you, thanks for your explanation. You don't need to show "Nothing" value in the expected result, right?

 

I hope the following test can help you.

 

1. Execute "Unpivoted Columns" in Transform data (Power Query)

vxuxinyimsft_0-1738648177717.png

 

Select the Material and Months Cover columns, then Transform -> Unpivoted columns -> Unpivoted other columns

vxuxinyimsft_1-1738648178235.png

 

Close & Apply

vxuxinyimsft_0-1738648304460.png

 

2. Create a calculated table

 

Slicer = VALUES('Table'[Months Cover])

 

 

no relationship between two tables

vxuxinyimsft_4-1738648581868.png

 

3. Create a measure

 

Measure = 
VAR _selected = SELECTEDVALUE(Slicer[Months Cover])
RETURN
IF(MAX('Table'[Months Cover]) <= _selected && LEFT(MAX('Table'[Attribute])) = LEFT(_selected) && MAX('Table'[Value]) <> "Nothing", 1, 0)

 

 

Output:

vxuxinyimsft_1-1738648412057.png

 

vxuxinyimsft_2-1738648421648.png

If you need to display all values ​​when no slicer is selected, you can modify the measure:

Measure = 
VAR _selected = SELECTEDVALUE(Slicer[Months Cover])
RETURN
IF(_selected = BLANK(), 1, IF(MAX('Table'[Months Cover]) <= _selected && LEFT(MAX('Table'[Attribute])) = LEFT(_selected) && MAX('Table'[Value]) <> "Nothing", 1, 0))

 

Output:

vxuxinyimsft_3-1738648551354.png

Best Regards,
Yulia Xu

 

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

4 REPLIES 4
v-xuxinyi-msft
Community Support
Community Support

Hi @Jtbonner1986 

 

Thanks for the reply from Greg_Deckler .

 

The following test is for your reference.

 

1. Execute "Unpivoted Columns" in Transform data (Power Query)

vxuxinyimsft_0-1737687531492.png

 

Select the Material and Months Cover columns, then Transform -> Unpivoted columns -> Unpivoted other columns

vxuxinyimsft_1-1737687653674.png

 

Close & Apply

vxuxinyimsft_2-1737687952423.png

 

2. Create a measure as follows

Measure = IF(ISFILTERED('Table'[Attribute]), IF(MAX('Table'[Value]) <> "Nothing", 1, 0), 1)

 

Put the measure into the visual-level filters, set up show items when the value is 1.

vxuxinyimsft_3-1737688390079.png

 

Output:

vxuxinyimsft_4-1737688438457.png

 

Best Regards,
Yulia Xu

 

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

Hi Julia, 

in the example given if 1M weas selected, you should only filter the materials with 1M or less, so it should only show, material 123. 

 

if filtering 3M cover, it should show material 123 & 456.... 

Hi @Jtbonner1986 

 

Sorry for misunderstanding you, thanks for your explanation. You don't need to show "Nothing" value in the expected result, right?

 

I hope the following test can help you.

 

1. Execute "Unpivoted Columns" in Transform data (Power Query)

vxuxinyimsft_0-1738648177717.png

 

Select the Material and Months Cover columns, then Transform -> Unpivoted columns -> Unpivoted other columns

vxuxinyimsft_1-1738648178235.png

 

Close & Apply

vxuxinyimsft_0-1738648304460.png

 

2. Create a calculated table

 

Slicer = VALUES('Table'[Months Cover])

 

 

no relationship between two tables

vxuxinyimsft_4-1738648581868.png

 

3. Create a measure

 

Measure = 
VAR _selected = SELECTEDVALUE(Slicer[Months Cover])
RETURN
IF(MAX('Table'[Months Cover]) <= _selected && LEFT(MAX('Table'[Attribute])) = LEFT(_selected) && MAX('Table'[Value]) <> "Nothing", 1, 0)

 

 

Output:

vxuxinyimsft_1-1738648412057.png

 

vxuxinyimsft_2-1738648421648.png

If you need to display all values ​​when no slicer is selected, you can modify the measure:

Measure = 
VAR _selected = SELECTEDVALUE(Slicer[Months Cover])
RETURN
IF(_selected = BLANK(), 1, IF(MAX('Table'[Months Cover]) <= _selected && LEFT(MAX('Table'[Attribute])) = LEFT(_selected) && MAX('Table'[Value]) <> "Nothing", 1, 0))

 

Output:

vxuxinyimsft_3-1738648551354.png

Best Regards,
Yulia Xu

 

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

Greg_Deckler
Super User
Super User

@Jtbonner1986 You need a Complex Selector: The Complex Selector - Microsoft Fabric Community



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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