Forming AND condition between slicers of connection less tables

[prateekraina]

Hi Everyone,

I have a very interesting scenario and would appreciate your help in resolving this.

I have a sample data which looks like below:

Skill Name	Competency Code	Name
English	7	P1
Data Developer	4	P1
SQL Server	2	P1
C Sharp	1	P1
English	7	P2
English	7	P3
Data Developer	4	P3
SQL Server	2	P3
C Sharp	2	P3

Problem Statement: I need to put 3 slicers on the report namely Primary Skill, Secondary Skill and Other based out of [Skill Name] column in above data.
Each of the slicer will have an asscoated Competency Code Range. Primary is 5-7, Secondary is 3-4 and Other is 0-2.



So, when I choose English in Primary Skill slicer, Data Developer in Secondary and SQL Server in Other skills slicer, I should see the Names of Person who satisfy all these 3 conditions i.e We need to find a person who has all these 3 skills.

Expected Output:

Name	Skill
P1	English
P1	Data Developer
P1	SQL Server
P3	English
P3	Data Developer
P3	SQL Server

Solution Tried:

I have created 3 connection less tables for Primary, Secondary and Other Skills and Competecy.
Then I have tried to identify the selected Skill and Competency Code thorugh below measure for Primary, Secondary and Other slicers and marked the grid rows as 1/0.

IF(
    SELECTEDVALUE(Data[Skill Name]) = [Primary Skill]
    && MAX(Data[Competency Code]) >= [Primary Min Competency Code]
    && MAX(Data[Competency Code]) <= [Primary Max Competency Code]
    ,1
    ,0
)

I am not however able to club the result together. Its doing kind of OR between them.


It would be greatly appreciated if someone could try this out. This has driven me crazy I should mention that.

-Prateek Raina

[v-cherch-msft · Accepted solution]

Hi @prateekraina 

You may create primary,secondary,other measures with the IF condition.Then get the output as below:

Primary = IF(MAX(Data[Skill Name])=MAX(Primary[Primary Skill]) &&MAX(Data[Competency Code])>=MAX(Primary[Competency code min])&&MAX(Data[Competency Code])<=MAX(Primary[Competency code max]),1,0)

Output = 
VAR a =
    CALCULATETABLE ( VALUES ( Data[Name] ), FILTER ( ALL ( Data ), [Primary] = 1 ) )
VAR b =
    CALCULATETABLE ( VALUES ( Data[Name] ), FILTER ( ALL ( Data ), [Second] = 1 ) )
VAR c =
    CALCULATETABLE ( VALUES ( Data[Name] ), FILTER ( ALL ( Data ), [Other] = 1 ) )
RETURN
    IF (
        AND (
            MAX ( Data[Name] ) IN a
                && MAX ( Data[Name] ) IN b
                && MAX ( Data[Name] ) IN c,
            [Primary] = 1
                || [Second] = 1
                || [Other] = 1
        ),
        1
    )

Regards, 

[prateekraina]

@v-cherch-msft  Thank you for the help. This was perfect.

-Prateek Raina