Forum Discussion
Helper IChanging slicer condition to AND
Hi all,
I am trying to work this out for ages. I found many different solutions online, but nothing worked. It's a simple one table one slicer solution. I need to filter the ID by the selected rules in the slicer. PBI default is OR condition, but I need to change it to AND.
data:
IDRule
| 1 | a |
| 2 | a |
| 2 | b |
| 3 | c |
| 3 | a |
| 3 | d |
| 4 | b |
| 4 | a |
| 5 | b |
| 6 | d |
| 6 | e |
| 7 | d |
| 7 | e |
| 7 | a |
| 7 | f |
| 7 | g |
| 8 | d |
| 8 | a |
| 9 | e |
| 10 | a |
| 10 | d |
Thanks 🙂
WOLFIE , Try Measure
measure =
var _cnt = calculate(DistinctCount('Table'[Rule]) ,allselected('Table'))
return
countx(filter(summarize('Table', 'Table'[ID], "_1", DistinctCount('Table'[Rule])),[_1]=_cnt),[ID])or
measure =
var _cnt = calculate(DistinctCount('Table'[Rule]) ,allselected('Table'))
return
calculate(countx(filter(summarize('Table', 'Table'[ID], "_1", DistinctCount('Table'[Rule])),[_1]=_cnt),[ID]), filter(allselected(Table), Table[ID] =max(Table[ID])))amitchandak Quick additional question 🙂 I need the table to keep all values when nothing is selected in the slicer. I tried this, but it doesn't work:
measure =VAR _cnt = CALCULATE(DISTINCTCOUNT('Customer'[Rule]) ,ALLSELECTED('Customer'))RETURNIF(ISFILTERED(Customer),CALCULATE(COUNTX(FILTER(SUMMARIZE('Customer', 'Customer'[ID], "_1",DISTINCTCOUNT('Customer'[Rule])),[_1]=_cnt),[ID]),FILTER(ALLSELECTED(Customer),'Customer'[ID] = MAX('Customer'[ID]))),1)
4 Replies
- amitchandak
Super User
WOLFIE , Try Measure
measure =
var _cnt = calculate(DistinctCount('Table'[Rule]) ,allselected('Table'))
return
countx(filter(summarize('Table', 'Table'[ID], "_1", DistinctCount('Table'[Rule])),[_1]=_cnt),[ID])or
measure =
var _cnt = calculate(DistinctCount('Table'[Rule]) ,allselected('Table'))
return
calculate(countx(filter(summarize('Table', 'Table'[ID], "_1", DistinctCount('Table'[Rule])),[_1]=_cnt),[ID]), filter(allselected(Table), Table[ID] =max(Table[ID])))- WOLFIE
The second one works! 🙂 Thanks so much!!!
- WOLFIE
amitchandak Quick additional question 🙂 I need the table to keep all values when nothing is selected in the slicer. I tried this, but it doesn't work:
measure =VAR _cnt = CALCULATE(DISTINCTCOUNT('Customer'[Rule]) ,ALLSELECTED('Customer'))RETURNIF(ISFILTERED(Customer),CALCULATE(COUNTX(FILTER(SUMMARIZE('Customer', 'Customer'[ID], "_1",DISTINCTCOUNT('Customer'[Rule])),[_1]=_cnt),[ID]),FILTER(ALLSELECTED(Customer),'Customer'[ID] = MAX('Customer'[ID]))),1)- WOLFIE
SOLVED 🙂