Mutually Exclusive Member count

[SAIKIRANCHAVALI]

Hi All,

Issues calculating mutually exclusive member counts.

i have data like this:

RED	WHITE	YELLOW	BLUE	P1	P2	P3	P4
Y	Y	Y	N	1	1	1
Y	N	Y	N	1		1
N	N	Y	N			1
Y	N	Y	Y	1		1	1
N	Y	N	Y		1		1
Y	Y	N	N	1	1
Y	N	N	N	1

Note: P1,P2,P3&P4 where ever Y is there i consider it 1

 following steps:

1.Imported data 

2.Selected unpovited columns RED,WHITE,YELLOW& BLUE(here my data is duplicated)

3.Again i unpovied colums P1,P2,P3&P4(Here number of records increased)

4. my data like this 

A	B	C	D
RED	Y	P1	1
RED	N	P2	1
YELLOW	Y	P4	1
WHITE	Y	P3	1
RED	N	P2	1

5.Now i created mapping file 

Slicer_name	value
RED	P1
WHITE	P2
YELLOW	P3
BLUE	P4

6.i marged 4&5 tables based on VALUE and C

7.i got output like this

A	B	C	D	Slicer_name
RED	Y	P1	1	RED
RED	N	P2	1	RED
YELLOW	Y	P4	1	YELLOW
WHITE	Y	P3	1	WHITE
RED	N	P2	1	RED

8.i created four table P1,P2,P3&P4 same as below

Column

RED

YELLOW

WHITE

BLUE

9.i) created Four Measures

example:

p1=var x=selectedvalue(p1(column))

retrun

if(x=selectedvalue(A),calculate(count(b),filter(sheet1(b)="Y"),sheet1(slicer_name)=x))

ii)

p2=var x=selectedvalue(p1(column))

var y=selectedvalue(p2(clumns))

retrun

if(x=selectedvalue(A),calculate(count(b),filter(sheet1(b)=y),sheet1(value)="N",sheet1(slicer_name)=x))

"Till here i got correct values"

10. Output:

Columns	FInal
RED	5
YELLOW	6

this output dynamically changing the data.

11. Now i'm not able to do P3 & P4

(Note: if user select any priority next priority first priority members are mutually excluding)

Example: if user select P1=red(Y=3)

                                    P2=white(Y=1)

                                    P3=yellow(Y=1)

user select dynamically change data.

My logic is working P1&P2 but P3& P4 im getting worng Values.

Please Helping me this,

Any suggeste Apprisated,

Best Records,

Sai Kiran.

[v-alq-msft]

Hi, @SAIKIRANCHAVALI 

Based on your description, I assume that you want to count based on the priorities. I created data to reproduce your scenario. The pbix file is attached in the end.

Sample:

After unpivot four colors:

Then you may create four calculated tables and a measure as below.

Calculated table:
P1 = DISTINCT('Sample'[Attribute])
P2 = DISTINCT('Sample'[Attribute])
P3 = DISTINCT('Sample'[Attribute])
P4 = DISTINCT('Sample'[Attribute])

Measure:
Count = 
IF(
    HASONEVALUE(P1[Attribute])&&
    HASONEVALUE(P2[Attribute])&&
    HASONEVALUE(P3[Attribute])&&
    HASONEVALUE(P4[Attribute]),
    IF(
        SELECTEDVALUE(P1[Attribute])<>SELECTEDVALUE(P2[Attribute])&&
        SELECTEDVALUE(P2[Attribute])<>SELECTEDVALUE(P3[Attribute])&&
        SELECTEDVALUE(P3[Attribute])<>SELECTEDVALUE(P4[Attribute])&&
        SELECTEDVALUE(P4[Attribute])<>SELECTEDVALUE(P1[Attribute]),
        var t1 = 
        CALCULATETABLE(
            DISTINCT('Sample'[id]),
            FILTER(
                ALL('Sample'),
                'Sample'[Attribute]=SELECTEDVALUE(P1[Attribute])&&
                'Sample'[Value]="Y"
            )
        )
        
        var t2 = 
        CALCULATETABLE(
            DISTINCT('Sample'[id]),
            FILTER(
                ALL('Sample'),
                'Sample'[id] in t1&&
                'Sample'[Attribute]=SELECTEDVALUE(P2[Attribute])&&
                'Sample'[Value]="Y"
            )
        )
        
        var t3 = 
        CALCULATETABLE(
            DISTINCT('Sample'[id]),
            FILTER(
                ALL('Sample'),
                'Sample'[id] in t2&&
                'Sample'[Attribute]=SELECTEDVALUE(P3[Attribute])&&
                'Sample'[Value]="Y"
            )
        )
        
        var t4 = 
        CALCULATETABLE(
            DISTINCT('Sample'[id]),
            FILTER(
                ALL('Sample'),
                'Sample'[id] in t3&&
                'Sample'[Attribute]=SELECTEDVALUE(P4[Attribute])&&
                'Sample'[Value]="Y"
            )
        )
        
        return
        SUMX(
            SUMMARIZE(
                Selections,
                Selections[Selections],
                "Re",
                SWITCH(
                    SELECTEDVALUE(Selections[Selections]),
                    "P1",COUNTROWS(t1),
                    "P2",COUNTROWS(t2),
                    "P3",COUNTROWS(t3),
                    "P4",COUNTROWS(t4)
                )
            ),
            [Re]
    )
)
)

Result:

Best Regards

Allan

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

[SAIKIRANCHAVALI]

Hi,

You did a great job. Thanks for replying, But there are some issues which this report. I am getting wrong count for P2,P3 and P4. when  selecting different colors for priority again count is coming wrong.

Sample data.

1.If user selects dynamically P1,P2,P3 & P4. i am getting P1 value correct but P2,P3 & P4 are having wrong counts.

Sample selection:

Correct count should be: 

                 P1(red)=3

                P2(black)=2

                P3(blue)=0

                P4(white)=1

2. I want to see the counts, If user selects any 1 , 2 or 3 priority. it's not compulsorily that user everytime will select all the priorities. He can select minimum 1 and maximum 4 priority.

Sample selection:

Thanks,

Sai Kiran

[v-alq-msft]

Hi, @SAIKIRANCHAVALI 

Could you please explain to us the following result? I am not very clear about it. Thanks.

P1(red)=3

P2(black)=2

P3(blue)=0

P4(white)=1

Best Regards

Allan

[SAIKIRANCHAVALI]

Hi ,

According to the sample data

Example 1:

1)if Priority 1 is Red then count of Y in Red column is 3,

2)if Priority 2 is black then 1st priority(Red) should be N then take Y’s count of Black in same row i.e. 2 (Based on 1st priority our 2nd priority depend)

3)if priority 3 is Blue then 1st (Red) and 2nd (Black) priority should be N then Take Blue’s Y count in  same row i.e. 0 (Based on 1st         and 2nd priority our 3rd priority depends)

4)if priority 4 is white then 1st,2nd and 3rd priority should be N Then White value should be Y on that row i.e. 1.

NOTE: Here it’s a mutually exclusive method, which means if one member is eligible for 1 colour then He/she shouldn’t be eligible for next priority count.

Example 2:

Selection	Total Counts
P1(White)	4
P2(Blue)	0
P3(Black)	1
P4(Red)	1

if P3 is none then user cannt selection next priority that is P4 then show the P1&P2 count's only.

Thanks,

Sai Kiran

[Fowmy]

@SAIKIRANCHAVALI 

Wher is your source data and how does look? And, what kind of output are you expecting?
Please share some sample data, 

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS  to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[SAIKIRANCHAVALI]

Sample Data:

id	blue	red	black	white
1	Y	Y	Y	Y
2	N	Y	N	N
3	N	N	Y	N
4	N	N	N	Y
5	Y	Y	N	Y
6	Y	N	Y	Y

In visulation:

i need to sow nour slicers

P1,P2,P3&P4

P1

RED

WHITE

BLUE

BLACK

simillarly P2,P3 & P4

i need Output:

If user select dynamically P1,P2,P3&P4 selections

Note:P1,P2,P3&P4 single slections

Example: user select P1=Red next priority P2(selction Options)=White,BLue,Black , simillarly P3&P4

High importance:

one person eligible One Priority, Next Priority he wont come.

User select dynamically.

Output:

Selection	Total Count
P1(red)	3
P2(white)	2
P3(Blue)	0
P4(Black)	1

Note: Total count should be all Y counts.

Thanks,

Saikiran