CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Maho Lau
Maho Lau

Reputation: 47

Or condition using slicers (one column)

So I have a column that its a concate of some strings, something like this enter image description here

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

enter image description here

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

enter image description here

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 everything

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

-----------------Edit-----------------

I would add the relationship of the table and the new ones that I made

enter image description here

And the measure that I using to count

Sum under OR condition =

VAR OR_Rows =
    FILTER ( tJobs, [OR_ACS] = 1 )
RETURN
    CALCULATE ( SUM ( tJobs[Num_ACS] ), OR_Rows )

Upvotes: 0

Views: 155

Answers (1)

Luke_0
Luke_0

Reputation: 855

From your linked article:

These new tables will ultimately be used as our slicers, but in this scenario it is important to ensure that these new tables do not have a relationship with the main data table. Conventional filtering would require a relationship between the tables for filters to propagate between them, but remember that we are not doing conventional filtering.

If you delete your relationships between the fact table and your A_ dimensions, the slicers should work as intended.

Upvotes: 0

Related Questions