One slicer to select 3 boolean (0/1) columns in the same table

[Anonymous]

Dear community,

I am stuck at a challenge regarding how to make a slicer with multiple selection filter a table based on 3 columns.

The 3 columns have names FlagError#1, FlagError#2 and FlagError#3. Each of these columns will alway be a integer of either 0 or 1. Further, there can be any combination within these 3 columns, 0-0-0, 0-0-1, ... all the way up to 1-1-1.

The slicer is multi-select, it has one row corresponding to the 3 columns, and, very important, it must use OR condition among selections. That is, if ie the user selects "FlagError#1" and "FlagError#3", the results should be all rows that has either 0 in the FlagError#1 and 1 in the FlagError#3, 1 in FlagError#1 and 0 in FlagError#3 or 1 in both of these columns. if the user selects all three rows in the slicer, any rows that has 1 in at least one of the corresponding columns, should be shown.

I need to be able to define a text in the slicer for each of the 3 selections.

Any help would be very much appreciated, thanks!

BR

Nortor

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,  

you could use the following Steps:

Step 1，create a table  for slicer:

Step 2, use the following measure:

Measure =

var test1=CALCULATE(max('Table'[FlagError＃1]),FILTER(('Table'),'Table'[FlagError＃1]=1))

var test2=CALCULATE(max('Table'[FlagError＃1]),FILTER(('Table'),'Table'[FlagError＃2]=1))

var test3=CALCULATE(max('Table'[FlagError＃1]),FILTER(('Table'),'Table'[FlagError＃3]=1))

var test4=CALCULATE(max('Table'[FlagError＃1]),FILTER(('Table'),'Table'[FlagError＃3]=1||'Table'[FlagError＃2]=1))

var test5=CALCULATE(max('Table'[FlagError＃1]),FILTER(('Table'),'Table'[FlagError＃3]=1||'Table'[FlagError＃3]=1))

var test6=CALCULATE(max('Table'[FlagError＃1]),FILTER(('Table'),'Table'[FlagError＃3]=1||'Table'[FlagError＃2]=1||'Table'[FlagError＃3]=1))

var a4=

IF(SELECTEDVALUE('Table (2)'[Name])="FlagError＃1",test1,

IF(SELECTEDVALUE('Table (2)'[Name])="FlagError＃2",test2,

IF(SELECTEDVALUE('Table (2)'[Name])="FlagError＃3",test3,

IF(FIRSTNONBLANK('Table (2)'[Name],'Table (2)'[Name])="FlagError＃1"||FIRSTNONBLANK('Table (2)'[Name],'Table (2)'[Name])="FlagError＃2" ,test4,

IF(FIRSTNONBLANK('Table (2)'[Name],'Table (2)'[Name])="FlagError＃1"||FIRSTNONBLANK('Table (2)'[Name],'Table (2)'[Name])="FlagError＃3" ,test5,

IF(FIRSTNONBLANK('Table (2)'[Name],'Table (2)'[Name])="FlagError＃1"||FIRSTNONBLANK('Table (2)'[Name],'Table (2)'[Name])="FlagError＃2"||FIRSTNONBLANK('Table (2)'[Name],'Table (2)'[Name])="FlagError＃3" ,test6,

blank()))))))

return a4

final you will get what you want !

click here   to download pbix  file if you need!

Best Regard

Lucien Wang

[v-luwang-msft]

Hi @Anonymous ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution to help others find it.

Best Regards

Lucien 

[Anonymous]

File for testing .pbix

http://www.filedropper.com/slicertest

[v-xulin-mstf]

Hi @Anonymous,

I download the pbix you provided, then delete the measure and check the data，see the below,the base data is duplicates.You could check the base data again. Wish it is helpful for you.

Best regard

Lucien Wang

[Anonymous]

Dear Lucien, 

Sorry about that. I have now corrected the data, and there should only be unique FactIDs.

I still get several rows in the table if I select 2 or 3 error in the slicer. If the user selects for example error1 and error2, it should show rows that have error in both these or in one of them.

File is here:
http://www.filedropper.com/slicertest_1

Thanks for your continued support!

BR

Nortor

[v-xulin-mstf · Accepted solution]

Hi @Anonymous,

I use my own DAX, and in your data, everything is fine, and I guess that the fact that you have duplicate data may be related to the modification of Dax.

mSlicerBrudd =

var test1=CALCULATE(max('tbl_Facts'[Error1]),FILTER(('tbl_Facts'),'tbl_Facts'[Error1]=1))

var test2=CALCULATE(max('tbl_Facts'[Error1]),FILTER(('tbl_Facts'),'tbl_Facts'[Error2]=1))

var test3=CALCULATE(max('tbl_Facts'[Error1]),FILTER(('tbl_Facts'),'tbl_Facts'[Error3]=1))

var test4=CALCULATE(max('tbl_Facts'[Error1]),FILTER(('tbl_Facts'),'tbl_Facts'[Error3]=1||'tbl_Facts'[Error2]=1))

var test5=CALCULATE(max('tbl_Facts'[Error1]),FILTER(('tbl_Facts'),'tbl_Facts'[Error3]=1||'tbl_Facts'[Error3]=1))

var test6=CALCULATE(max('tbl_Facts'[Error1]),FILTER(('tbl_Facts'),'tbl_Facts'[Error3]=1||'tbl_Facts'[Error2]=1||'tbl_Facts'[Error3]=1))



var a4=

IF(SELECTEDVALUE('tbl_Slicer'[Name])="Error#1",test1,

IF(SELECTEDVALUE('tbl_Slicer'[Name])="Error#2",test2,          

IF(SELECTEDVALUE('tbl_Slicer'[Name])="Error#3",test3,

IF(FIRSTNONBLANK('tbl_Slicer'[Name],'tbl_Slicer'[Name])="Error#1"||FIRSTNONBLANK('tbl_Slicer'[Name],'tbl_Slicer'[Name])="Error#2" ,test4,

IF(FIRSTNONBLANK('tbl_Slicer'[Name],'tbl_Slicer'[Name])="Error#1"||FIRSTNONBLANK('tbl_Slicer'[Name],'tbl_Slicer'[Name])="Error#3" ,test5,

IF(FIRSTNONBLANK('tbl_Slicer'[Name],'tbl_Slicer'[Name])="Error#1"||FIRSTNONBLANK('tbl_Slicer'[Name],'tbl_Slicer'[Name])="Error#2"||FIRSTNONBLANK('tbl_Slicer'[Name],'tbl_Slicer'[Name])="Error#3" ,test6,



blank()))))))

return a4 

Here is the output:

Best regard

Lucien Wang

[Anonymous]

Dear Lucien! 

Thank you for the follow-up! I have attached my .pbix example file. I get several lines for the ID, even though it is unique in the data table.

Warning; I did modify the code a little, so hopefully it is just a small mistake. I have added a variant for no selection in the slicer (var 0), just so it is possible to see all rows, also those without errors.

Further I added var 7 to handle all combinations of slicers. 

The slicer should be OR of nature, so that if user selects in slicer error1 and error2, rows with error ("1") in both these columns, and in either one of these should be displayed. 

Thank you again for your assistance, I am very grateful!

BR

NT

[v-xulin-mstf]

Hi @Anonymous,

You can upload the .pbix file to onedrive for business and share the link with me.

Best Regard

Lucien Wang

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,  

you could use the following Steps:

Step 1，create a table  for slicer:

Step 2, use the following measure:

Measure =

var test1=CALCULATE(max('Table'[FlagError＃1]),FILTER(('Table'),'Table'[FlagError＃1]=1))

var test2=CALCULATE(max('Table'[FlagError＃1]),FILTER(('Table'),'Table'[FlagError＃2]=1))

var test3=CALCULATE(max('Table'[FlagError＃1]),FILTER(('Table'),'Table'[FlagError＃3]=1))

var test4=CALCULATE(max('Table'[FlagError＃1]),FILTER(('Table'),'Table'[FlagError＃3]=1||'Table'[FlagError＃2]=1))

var test5=CALCULATE(max('Table'[FlagError＃1]),FILTER(('Table'),'Table'[FlagError＃3]=1||'Table'[FlagError＃3]=1))

var test6=CALCULATE(max('Table'[FlagError＃1]),FILTER(('Table'),'Table'[FlagError＃3]=1||'Table'[FlagError＃2]=1||'Table'[FlagError＃3]=1))

var a4=

IF(SELECTEDVALUE('Table (2)'[Name])="FlagError＃1",test1,

IF(SELECTEDVALUE('Table (2)'[Name])="FlagError＃2",test2,

IF(SELECTEDVALUE('Table (2)'[Name])="FlagError＃3",test3,

IF(FIRSTNONBLANK('Table (2)'[Name],'Table (2)'[Name])="FlagError＃1"||FIRSTNONBLANK('Table (2)'[Name],'Table (2)'[Name])="FlagError＃2" ,test4,

IF(FIRSTNONBLANK('Table (2)'[Name],'Table (2)'[Name])="FlagError＃1"||FIRSTNONBLANK('Table (2)'[Name],'Table (2)'[Name])="FlagError＃3" ,test5,

IF(FIRSTNONBLANK('Table (2)'[Name],'Table (2)'[Name])="FlagError＃1"||FIRSTNONBLANK('Table (2)'[Name],'Table (2)'[Name])="FlagError＃2"||FIRSTNONBLANK('Table (2)'[Name],'Table (2)'[Name])="FlagError＃3" ,test6,

blank()))))))

return a4

final you will get what you want !

click here   to download pbix  file if you need!

Best Regard

Lucien Wang

[Anonymous]

Dear Lucien!

Thank you so much for taking the time to (almost) solve this for me! Very very much appreciated, as I was completely stuck.

There are some small things I would love to have clarified; the main table where the FlagErrors are (3 columns), have one column with an unique id / key for each row. I have double-checked, and there are no duplicates. Still, when I use the code, it seems to repeat the id on several rows. I suspect this has to do with the use of the MAX in CALCULATE in the measure.

Would you have any suggestions on how I make sure that the rows are listen only once?

Again thank you for your time and efforts!

Best regards,

Nortor

[v-xulin-mstf]

Hi @Anonymous,

I tried according to the data in your screenshot, but it does not appear as you said, could pls provide your pbix file ? Remember to remove the confidential information.

Best Regard

Lucien Wang

[Anonymous]

Please check your messages, I do not know how to share my pbix, I am not allowed to upload directly in messages...