Or condition using slicers (one column)

[majo23]

Hi everyone,

So I have a column that its a concate of some strings, something like this 

and what I did was make a measure that counts how many time a word repeated, something like this

I also have as many slicers as columns that I made, but when I want to know how many ARTC & Climber are in the firts colum (ACS) the result is the AND condition

I try to follow what this person did https://apexinsights.net/blog/or-xor-slicing but it give me the same result with AND condition:

I follow this steps : 

Made a column in the main table for each string that have that word (in this case ACS)

AJob_ACS = IF (CONTAINSSTRING ((tJobs[TaskRequirements]),"ACS"), tJobs[TaskRequirements],BLANK())

Made a calculatetable for each string

A_ACS = CALCULATETABLE(VALUES(tJobs[TaskRequirements]), FILTER(tJobs, CONTAINSSTRING(tJobs[TaskRequirements], "ACS")))

Then made the OR filter 

OR_ACS =

IF(

    SELECTEDVALUE( tJobs[AJob_ACS]) IN ALLSELECTED( A_ACS[TaskRequirements]) &&

    (SELECTEDVALUE( tJobs[AJob_ARTC]) IN ALLSELECTED( A_ARTC[TaskRequirements]) ||

    SELECTEDVALUE( tJobs[AJob_Climber]) IN ALLSELECTED( A_Climber[TaskRequirements]) ||

    ...),

1, 0)

And count everythin 

Col_ACS = IF([OR_ACS] = 1 || CONTAINSSTRING ((tJobs[TaskRequirements]),"ACS") , 1, 0)

Im using the Col_ACS, ..... columns on the slicers (one for each slicer)

And also for the table matrix

If someone have any suggestion, I would appreciated

[v-yueyunzh-msft]

Hi, @majo23 

According to your description, I don't quite understand the purpose of creating so many slicers. Do you want to choose two slicers to control whether this row contains one of these two values?

If this , you can refer to this:
(1)This is my test data:

(2)We can create three tables like this:

(3)We do not need to create ant relationships between them , then we can create two measure like this:

Measure = var _acs = MAX('Col_ACS'[Column1])
var _artc=MAX('Col_ARTC'[Column1])
var _climber = max('Col_Climber'[Column1])
var _list  = {_acs,_artc,_climber}
var _cur_row  =MAX('Table'[TaskRequirements])
var _list2 =  FILTER(_list,[Value]<> BLANK())

return
IF(COUNTROWS(FILTER(_list2,CONTAINSSTRING(_cur_row,[Value]))), 1,0)

Measure 2 = var _t  = FILTER(ADDCOLUMNS( ALLSELECTED('Table'[Index],'Table'[TaskRequirements]) , "flag" , [Measure]) , [flag] = 1)
var _t2 = ADDCOLUMNS(_t , "ACS" , IF( CONTAINSSTRING( [TaskRequirements] , "ACS" ) ,1,0) , "ARTC" ,IF( CONTAINSSTRING( [TaskRequirements] , "ARTC" ) ,1,0),"Climber",IF( CONTAINSSTRING( [TaskRequirements] , "Climber" ) ,1,0))
var _row = MAX('Row'[Column1])
return
SWITCH(_row,
"ACS",SUMX(_t2 , [ACS]),
"ARTC",SUMX(_t2,[ARTC]),
"Climber" , SUMX(_t2, [Climber]))

(4)Then we can put the fields on the visual and we can get this result:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[majo23]

Hi @v-yueyunzh-msft thanks again for you reply I get the idea that you suggest and apply it, but I have 22 differents Slicers (so I have to made 22 tables for each) is it possible to apply another solution maybe with a measure ? Here AND OR.pbix what I want is to apply the or logic into the slicers for the table that we have below, that single column that we have contain a string of 1 or more thats why I made columns for each task so I can count it in the table, insted of applying the slicers as an and logic (by default) how could I change it to or for each column? 

[v-yueyunzh-msft · Accepted solution]

Hi , @majo23 

Thanks for your response!

I checked your pbix file. For your needs, I think you can directly use the field as the column field of Matrix and then use [Measure2] as the value, you can refer to this pbix file below.

And for your pbix , you need to delete all the relationship between tables.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly