Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have 2 columns in my table with Yes/No values.
I have built 2 slicers for each column.
If both slicers are set to Yes, I would like a 3rd slicer with AND or OR values to switch between the records.
This is the way I've tried to resolve this unsuccessfully. If I should be going about it another way let me know. I feel like i'm missing something basic here.
I created a new table
Solved! Go to Solution.
Hi,
Thanks for the solution @ryan_mayu and @ToddChitt offered, and i want to offer some more information for user to refer to.
hello @adoster , you can refer to the following sample.
1.Create three new tables(there is no relationship among tables)
Slicer A
Slicer B
And/or table.
2.Create a measure.
MEASURE =
IF (
ISFILTERED ( 'And/Or'[Type] ),
SWITCH (
SELECTEDVALUE ( 'And/Or'[Type] ),
1,
CALCULATE (
COUNTROWS ( 'Table' ),
'Table'[ColumnA] IN VALUES ( SlicerA[Value] ),
'Table'[ColumnB] IN VALUES ( SlicerB[Value] )
),
0,
CALCULATE (
COUNTROWS ( 'Table' ),
OR (
'Table'[ColumnA] IN VALUES ( SlicerA[Value] ),
'Table'[ColumnB] IN VALUES ( SlicerB[Value] )
)
)
),
CALCULATE (
COUNTROWS ( 'Table' ),
'Table'[ColumnA] IN VALUES ( SlicerA[Value] ),
'Table'[ColumnB] IN VALUES ( SlicerB[Value] )
)
)
3.Then put the values of three new tables to three slicers, and put the measure and the related field in original table to a table visual.
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thanks for the solution @ryan_mayu and @ToddChitt offered, and i want to offer some more information for user to refer to.
hello @adoster , you can refer to the following sample.
1.Create three new tables(there is no relationship among tables)
Slicer A
Slicer B
And/or table.
2.Create a measure.
MEASURE =
IF (
ISFILTERED ( 'And/Or'[Type] ),
SWITCH (
SELECTEDVALUE ( 'And/Or'[Type] ),
1,
CALCULATE (
COUNTROWS ( 'Table' ),
'Table'[ColumnA] IN VALUES ( SlicerA[Value] ),
'Table'[ColumnB] IN VALUES ( SlicerB[Value] )
),
0,
CALCULATE (
COUNTROWS ( 'Table' ),
OR (
'Table'[ColumnA] IN VALUES ( SlicerA[Value] ),
'Table'[ColumnB] IN VALUES ( SlicerB[Value] )
)
)
),
CALCULATE (
COUNTROWS ( 'Table' ),
'Table'[ColumnA] IN VALUES ( SlicerA[Value] ),
'Table'[ColumnB] IN VALUES ( SlicerB[Value] )
)
)
3.Then put the values of three new tables to three slicers, and put the measure and the related field in original table to a table visual.
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
While this technically works it requires a new table for every slicer which at scale is cumbersome. I have about 10 values at this time requiring an AND/OR option so this may work short term, but likely will submit an idea for this type of functionality to be implemented similar to what Tableau offers unless there is an alternative solution. Thank you for this workaround.
RecordNum | ColumnA | ColumnB | |
1 | Yes | No | |
2 | Yes | Yes | |
3 | No | Yes | |
Both slicers set to Yes, AND Count = 1 | |||
Both sliicers set to Yes, OR Count = 3 |
I would like a slicer on screen with AND or OR values to switch between these but also this needs to be scalable across many column, not just 2.
pls try this
Proud to be a Super User!
Thanks for the suggestion but may be I did not explain properly.
There are 2 existing slicers on screen for Column A & B with Yes/No values. Then I need a 3rd slicer with the ability to adjust count between AND/OR values of slicers on screen (scalable to many more slicers).
Yolo Zhu offered a decent workaround for a couple slicers and I'll submit an idea for further functionality.
Sorry, but I don't understand this statement:
"If both slicers are set to Yes, I would like a 3rd slicer with AND or OR values to switch between the records."
What problem are you trying to solve? Can you give us sample data outputs based on slicer values?
Proud to be a Super User! | |
User | Count |
---|---|
111 | |
105 | |
100 | |
77 | |
44 |
User | Count |
---|---|
143 | |
142 | |
122 | |
100 | |
57 |