Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I've got data roughly as below:
Part Number | System | Pressure | Size |
A1929 | Horizontal | 1000bar | 5mm |
A2910 | Horizontal + Vertical | 1000bar | 7mm |
A2819 | Horizontal | 1500bar | 5mm+7mm |
A2839 | Vertical | 1000bar + 1500bar | 5mm |
A5463 | Horizontal + Vertical | 1500bar | 7mm |
A4563 | Horizontal | 1000bar + 1500bar | 5mm + 7mm |
I would like to create filters on the columns that would mean if I wanted to filter, for example, 'Horizontal', it would filter to lines 1, 2 and 3. Same for the other two columns. And also, then, I'd be able to build a fully filtered selection, such as 'Horizontal/1500bar/5mm'. I feel like there must be a simple way of doing this but struggling. I'd like to end with a dashboard that people can select their system and see the relevant part numbers filtered.
Thanks,
Solved! Go to Solution.
Hi, @Anonymous
You can create three calculated table as follows.
PressureSlicer = DISTINCT('Table'[Pressure])
SizeSlicer = DISTINCT('Table'[Size])
SystemSlicer = DISTINCT('Table'[System])
Then you may create three measures as follows.
ContainPressure =
IF (
SUMX (
FILTERS ( 'PressureSlicer'[Pressure] ),
IF (
CONTAINSSTRING ( SELECTEDVALUE ( 'Table'[Pressure] ), [Pressure] ),
1,
BLANK ()
)
) <> 0,
1,
0
)
ContainSize =
IF(
SUMX(
FILTERS(
SizeSlicer[Size]
),
IF(
CONTAINSSTRING(SELECTEDVALUE('Table'[Size]),[Size]),
1,
BLANK()
)
)<>0,
1,
0
)
ContainsSystem =
IF(
SUMX(
FILTERS(
SystemSlicer[System]),
IF(
CONTAINSSTRING(SELECTEDVALUE('Table'[System]),[System]),
1,
BLANK()
)
)<>0,
1,
0
)
Finally, you may put these measures in visual level filters and configure as follows.
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
You can put fields 'System', 'Pressure', and 'Size' into slicer visuals, put the your desired fields into Table visual. When you make some selections in slicers, the table visual will return corresponding result. If you need the slicer works on the dashboard in Power BI service, you might click 'Pin a live Page' to pin the current page to the dashboard.
If I misunderstand your thoughts, please inform me of your expected output. I am glad to solve the problem for you.
Best Regards,
Allan
If this post helps, then please consider 'Accept it as the solution' to help the other members find it more quickly.
Thanks Allan. I maybe wasn't clear enough. What I'm trying to do is have a simplified filters, so that there is just one button to click for '1000bar' for example, which would filter '1000bar' and '1000bar + 1500bar'. And one buttong for '1500bar', which would filter '1000bar + 1500bar' and '1500bar'. The data i have is more complex than the example I've shown, so not simplifying it this way would make filtering the standard way, as you've shown, far more complicated and time consuming.
Hi, @Anonymous
You can create three calculated table as follows.
PressureSlicer = DISTINCT('Table'[Pressure])
SizeSlicer = DISTINCT('Table'[Size])
SystemSlicer = DISTINCT('Table'[System])
Then you may create three measures as follows.
ContainPressure =
IF (
SUMX (
FILTERS ( 'PressureSlicer'[Pressure] ),
IF (
CONTAINSSTRING ( SELECTEDVALUE ( 'Table'[Pressure] ), [Pressure] ),
1,
BLANK ()
)
) <> 0,
1,
0
)
ContainSize =
IF(
SUMX(
FILTERS(
SizeSlicer[Size]
),
IF(
CONTAINSSTRING(SELECTEDVALUE('Table'[Size]),[Size]),
1,
BLANK()
)
)<>0,
1,
0
)
ContainsSystem =
IF(
SUMX(
FILTERS(
SystemSlicer[System]),
IF(
CONTAINSSTRING(SELECTEDVALUE('Table'[System]),[System]),
1,
BLANK()
)
)<>0,
1,
0
)
Finally, you may put these measures in visual level filters and configure as follows.
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |