Check if the value of the measure is the same for all groups

[Anonymous]

Hi,

This is a simplified version of my data.

I have a table like this. This column changes based on the filters that the user selects, but this is specifically what I want to ckeck for.

Column

A

B

C

D

E

F

G

I want to build a measure to do this:

After filtering, if all available categories are repeated only once, return 0 for count of each category, else the real count.

Example 1:

Column

A

B

A

A

E

D

D

Return real counts because they are all not repeated once

Column	Count
A	3
B	1
D	2
E	1

Example 2:

Column

A

B

C

D

E

F

G

Return 0 because each repeated only one time.

Column	Count
A	0
B	0
C	0
D	0
E	0
F	0
G	0

Please help me in doing this task.

Thanks

[Anonymous]

Hi @Anonymous ,

1.To my knowledge,  ALLSELECTED() only considers the filters used to calculate the total. It will return all the functions of the selected color in the initial filter context.

So on my side, I used it to calculate the count of each selected category(filter in slicer).

2.For your additional condition, please modify the measure :

Measure = 
var _rows=COUNTX(ALLSELECTED('Table'),[Column]) // how many rows after filter
return IF(_rows=1 && [count]=1,1,IF(MAXX(ALLSELECTED('Table'),[count])=1 && MINX(ALLSELECTED('Table'),[count])=1, 0, [count]))

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

[Anonymous]

Thanks @Anonymous 

Based on what I learned here, I developed a new measure which does the same:

coun = COUNT('Table'[Index])
distin = DISTINCTCOUNTNOBLANK('Table'[Column])
coun_all = CALCULATE(coun, ALLSELECTED('Table'))
distin_all = CALCULATE(distin, ALLSELECTED('Table'))


Measure 2 = 
SWITCH(TRUE(),
[coun_all] = 1 && [distin_all] = 1, 1,
[coun_all] = [distin_all], 0, 
[coun])

This works fine.

Another way to simplify this is to define those four measures inside the measure2 as VAR. However, when I define them as VAR, the measure does not work correctly, and does not make values 0 when it should do. This is the one which does not work:

Measure 2 = 
VAR coun = COUNT('Table'[Index])
VAR distin = DISTINCTCOUNTNOBLANK('Table'[Column])
VAR coun_all = CALCULATE(coun, ALLSELECTED('Table'))
VAR distin_all = CALCULATE(distin, ALLSELECTED('Table'))

RETURN

SWITCH(TRUE(),
coun_all = 1 && distin_all = 1, 1,
coun_all = distin_all, 0, 
coun)

Can you please help me solve/understand why this is happening?

It seems I cannot attach file. Sorry for the incovenience. 

Thanks.

[Anonymous · Accepted solution]

Hi @Anonymous ,

1. If all categories are repeated only once, then 0 

2. As long as there is a category repeated multiple times, then all show the actual count.

If so, I use MAXX() and MINX() to decide based on overall categories like this:

count = CALCULATE(COUNTROWS('Table'),FILTER(ALLSELECTED('Table'),[Column]=MAX('Table'[Column])))

Measure = IF(MAXX(ALLSELECTED('Table'),[count])=1 && MINX(ALLSELECTED('Table'),[count])=1, 0, [count])

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

[Anonymous]

Nice solution!

For learning purposes, can you please explain what this section of the code does:

FILTER(ALLSELECTED('Table'),[Column]=MAX('Table'[Column]))

Also, how can I add the following rule to this measure:

If there is one category after filtering, don't make it zero, even though it is is 1. But if there is more than one category selected, make them zero if they are all 1.

Example 3:

Column

A

Column	Count
A	1

Example 4:

Column

A

B

Column	Count
A	0
B	0

Thanks.

-----------------------

Edit:

I found the solution to this new inquiry by adding the following SUMX condition:

Measure = IF(MAXX(ALLSELECTED('Table'),[count])=1 && MINX(ALLSELECTED('Table') && SUMX(ALLSELECTED('Table'),[count])>1, 0, [count])

Looking forward to the explanations. 

Thanks

[Anonymous]

Hi @Anonymous ,

Actually I'm not sure about the expected output you want. As the information you provided, I think @amitchandak 's method is right.

In case ,if you want to set 0 when the category appears only once after doing some filters then, you may try this:

Measure = 
var _count= CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Column]=MAX('Table'[Column])))
return IF(_count=1,0,_count)

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

[Anonymous]

Hi @Anonymous 

As I explained before and also in my other post here, the answer is not fully what I need. In your work, the left example is showing 0 for B and E category. However, it should show 1, because not all categories have repeated once. However, in the right tables, it is correct, since the count is 1 for each and all categories.

Please see my other reply for more explanations.

We need to define a dax that decides based on overall categories, and then apply its decision to each row (make them 0 or not). But I don't know how to do that.

Looking forward to the full solution.

[amitchandak]

@Anonymous , Try a new measure like

measure =
var _1 = calculate(count(Table[Column]), allselected(Table))
var _2 = calculate(distinctcount(Table[Column]), allselected(Table))
return
if(_1 = _2 , 0, count(Table[Column]))

[Anonymous]

@amitchandak 

Thanks for the measure. Your approach is really interesting.

However, there is one issue. In this solution, it makes it 0 or not per category. But, I want to make the decision based on all the categories. That is, if each/all filtered category is repeated only once, then return 0, else the real count (make the decision based on all, not partly) (see Example 1 I provided before).

How can I modify your solution to fulfill this? A measure that decides based on overall, while keeping other filters ....

Thanks.