Forum Discussion
Mutually Exclusive Member count
Hi, Anonymous
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.
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
Hi, Anonymous
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
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