Forum Discussion
Helper IFiltering 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
| Material | Months Cover | 1M Action | 3M Action | 6M Action |
| 123 | 1 | Tidy | Clean | Nothing |
| 456 | 3 | Clean | Tidy | Nothing |
| 789 | 6 | Nothing | Nothing | Nothing |
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:
| Material | Selected Action |
| 123 | Clean |
| 456 | Tidy |
Any help is so grateful
Cheers
Josh
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)
Select the Material and Months Cover columns, then Transform -> Unpivoted columns -> Unpivoted other columns
Close & Apply
2. Create a calculated table
Slicer = VALUES('Table'[Months Cover])no relationship between two tables
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:
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:
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- Greg_Deckler
Community Champion
Jtbonner1986 You need a Complex Selector: The Complex Selector - Microsoft Fabric Community
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)
Select the Material and Months Cover columns, then Transform -> Unpivoted columns -> Unpivoted other columns
Close & Apply
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.
Output:
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jtbonner1986Hi 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)
Select the Material and Months Cover columns, then Transform -> Unpivoted columns -> Unpivoted other columns
Close & Apply
2. Create a calculated table
Slicer = VALUES('Table'[Months Cover])no relationship between two tables
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:
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:
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
