How to calculate the count of a no unique column?

[jwbecalm]

If I have a table like below, Cell Name is unique, but Site Name contain one or more Cell Name,

and the socre for each Cell Name may be same or not.

Site Name	Cell Name	Score	Province
Site1	Cell1	300	A
Site2	Cell2	100	A
Site2	Cell3	50	A
Site3	Cell4	200	B
Site3	Cell5	400	B
Site3	Cell6	100	B
Site4	Cell7	200	B
Site4	Cell8	300	B
Site5	Cell9	200	C
Site5	Cell10	200	C
Site5	Cell11	200	C

Now if Want to Calculate: for different province,  the number of Site Name in which the distinct count of Score is greater than 1.
How do I write the measure?

The following is calcuate by manaualy.

because the distinct count of Score for each site is :

Site Name  distinct count of the Score

Site1           1

Site2           2 ( the distinct count of Score is greater than 1.)

Site3           3 ( the distinct count of Score is greater than 1.)

Site4           2 ( the distinct count of Score is greater than 1.)

Site5           1

So the final result shoule be:

Porvince    new measure(distinct Site Name in which distinct score >1 )

A               1(which is site2)

B               2(which is site3 and site4)

C               0

[Zubair_Muhammad]

Hi @jwbecalm

Just Put number in the VALUE field and Choose Distinct Count

[jwbecalm]

hi, Zubair, sorry for my description, the help is update, please help.thx

[Zubair_Muhammad]

@jwbecalm

This MEASURE hopefully

Measure =
COUNTX (
    FILTER (
        ALL ( TableName[Site Name] ),
        CALCULATE ( DISTINCTCOUNT ( TableName[Score] ) ) > 1
    ),
    CALCULATE ( DISTINCTCOUNT ( TableName[Site Name] ) )
)

[jwbecalm]

Thank you very much, @Zubair_Muhammad

But I am begineer of DAX, it's confusing to me...

I know the first parameter of COUNTX is a table, 

COUNTX(<table>,<expression>) 

So I try to copy this segment of code to create a new table

Filter table = FILTER (
        ALL ( TableName[Site Name] ),
        CALCULATE ( DISTINCTCOUNT ( TableName[Score] ) ) > 1
    )

 and I get this Filter table

Site Name

Site2

Site3

Site4

The next step is to use this Filter table in the COUNTX function:

COUNTX (
    Filter table,
    CALCULATE ( DISTINCTCOUNT ( TableName[Site Name] ) )
)

So the logic is :

step1:  get the site list where the distinct score is greater than 1 (FILTER function)

step2:, get the site count  int the site list(COUNTX function)

but how can step2 get result from the  Filter table: 

Site Name

Site2

Site3

Site4

this table don't have any other infomation.
 

May be my undertanding is wrong, please correct me, thx.

[Zubair_Muhammad]

@jwbecalm

You donot have to create a separate Table.

The MEASURE takes care of it.

You can think of it as a temporary table which MEASURE creates for the purpose of its MEASUREMENT

[jwbecalm]

Thank u, I understand a bit.

I just want to konw it better. so I deal it seperately.

I don't create a sepearate table, I just want to understand the result of each step.

Here, we must use ALL function to create the temp table? or we can use CALCULATETABLE to instead of? 

Because ALL seems have some filter igore function

ALL Function (DAX)

 

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.

Here in this scenario, we do not need the filter ignore function.

The second question is: 

Why the first DISTINCTCOUNT, you use CALCUATE before it: 

CALCULATE ( DISTINCTCOUNT ( 'tablename'[Score] ) ) > 1

but for the second one, you only use DISTINCTCOUNT?

 DISTINCTCOUNT ( 'tablename'[Site Name] ) )

[Zubair_Muhammad]

@jwbecalm

[Phil_Seamark]

Hi @jwbecalm

Please try this calculated measure

Measure = COUNTA('Table1'[Cell Name])

And add to a visual with just the 'Site Name' column

[jwbecalm]

sorry for my description, the help is update, please help.thx