Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to Solution.
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 Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 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....
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 Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Jtbonner1986 You need a Complex Selector: The Complex Selector - Microsoft Fabric Community
User | Count |
---|---|
141 | |
71 | |
70 | |
54 | |
53 |
User | Count |
---|---|
208 | |
95 | |
64 | |
61 | |
57 |