Get distinct count of names based on a table visual

[powerbidev123]

I am having a table visual something like below:

Name	Score
A	12
A	33
B	22
B	44
C	13
C	11

This table visual gets filtered by a slicer and the values get changed. There is a measure used in the table visual on visual level basis.

Problem Statement:

How can I get the count of the distinct names in a card Visual as per the slicer selection?

[powerbidev123 · Accepted solution]

Hi @mark_endicott , I tried this before the number is not changing upon slicer selection

[powerbidev123 · Accepted solution]

@mark_endicott  I have used a calculated table and created a column with distinct names in it  since I needed to use it in a measure which basically creates an 'AND' operation in slicer selection for multiple values

Names_selected=DISTINCT('Query1'[Name]) -- this is calculated table for the unique names

-- below is the measure for the 'AND ' operation on slicer selection

Measure =

IF (

    ISFILTERED ( Names_selected[Name])

        && MAX ( Query1[Name] )

            IN ALLSELECTED ( Names_selected[Name] )

                && COUNTROWS ( Names_selected)

                    <= CALCULATE (

                        COUNT ( Query1[Name] ),

                        FILTER (

                            ALLSELECTED ( Query1),

                            [Name]

                                IN ALLSELECTED ( Names_selected[Name] )

                                    && [Email] = MAX ( Query1[Email] )

                        )

                    ),

    1

)

I tried  adding this measure also in the card visual level filter so that the 'Query1'  table names can come as unique but the measure value I am unable to apply as it seems to be locked kind of thing. The same measure value I was able to apply it in the table visual 

[mark_endicott · Accepted solution]

@powerbidev123 - I'm sorry it's not clear why you need the calculated table. 

DISTINCTCOUNT( Query1[Name] ) will count the number of items selected, if the slicer uses the Query1[Name] column, therefore it will use your AND operation if there are two Names selected, see below:

It is however clear why your measure is not working. you are just asking it to produce the value 1 if your condition is TRUE, also your use of MAX will only ever take one value. 

You have now introduced Email into your measure, so there is no way I can solve this without some sample data, and all the other measures that may contribute to this. 

[powerbidev123 · Accepted solution]

Hi @mark_endicott ,  you are right on the part that max gives only 1 or 0 value. Let me reframe the question  a bit.  

Email	SkillName	Score
a	Amazon RDS	2
a	Automotive	1
d	Amazon RDS	1
d	Automotive	2
g	Amazon RDS	2
g	Automotive	2

Now I have put a slicer on SkillName in a separate query which gives distinct names

Dax used:  DISTINCT('Query1'[SkillName])

this above column I am using it in the AND operation measure in my main  query where the email, skillname,score reside which is as below:

Measure =

IF (

    ISFILTERED ( SkillsSelected[SkillName])  

        && MAX ( Query1[SkillName] )

            IN ALLSELECTED ( SkillsSelected[SkillName] )

                && COUNTROWS ( SkillsSelected)

                    <= CALCULATE (

                        COUNT ( Query1[SkillName] ),

                        FILTER (

                            ALLSELECTED ( Query1),

                            [SkillName]

                                IN ALLSELECTED ( SkillsSelected[SkillName] )

                                    && [Email] = MAX ( Query1[Email] )

                        )

                    ),

    1

)

Now the issue is the AND operation is working fine upon selecting from slicer but I need to display the count of the unique email in a separate card visual which I am unable to do as the measure gives only value as 1. The count in card visual that I need is 3 since 3 such unique emails are there which have both the Skills. 

[mark_endicott]

@powerbidev123 - Try this

DISTINCTCOUNT( 'Table'[Name] )

If this works, please accept it as the solution. 

[powerbidev123 · Accepted solution]

Hi @mark_endicott , I tried this before the number is not changing upon slicer selection

[mark_endicott]

@powerbidev123 - Then you need to provide more information about what column you have in your slicer. 

Does it come from the same table?

How does it relate to the Name column? 

If it is from a different table, is there a relationship between the tables?

If you have Name in the slicer and it is in the same table, it works fine:

[powerbidev123 · Accepted solution]

@mark_endicott  I have used a calculated table and created a column with distinct names in it  since I needed to use it in a measure which basically creates an 'AND' operation in slicer selection for multiple values

Names_selected=DISTINCT('Query1'[Name]) -- this is calculated table for the unique names

-- below is the measure for the 'AND ' operation on slicer selection

Measure =

IF (

    ISFILTERED ( Names_selected[Name])

        && MAX ( Query1[Name] )

            IN ALLSELECTED ( Names_selected[Name] )

                && COUNTROWS ( Names_selected)

                    <= CALCULATE (

                        COUNT ( Query1[Name] ),

                        FILTER (

                            ALLSELECTED ( Query1),

                            [Name]

                                IN ALLSELECTED ( Names_selected[Name] )

                                    && [Email] = MAX ( Query1[Email] )

                        )

                    ),

    1

)

I tried  adding this measure also in the card visual level filter so that the 'Query1'  table names can come as unique but the measure value I am unable to apply as it seems to be locked kind of thing. The same measure value I was able to apply it in the table visual 

[mark_endicott · Accepted solution]

@powerbidev123 - I'm sorry it's not clear why you need the calculated table. 

DISTINCTCOUNT( Query1[Name] ) will count the number of items selected, if the slicer uses the Query1[Name] column, therefore it will use your AND operation if there are two Names selected, see below:

It is however clear why your measure is not working. you are just asking it to produce the value 1 if your condition is TRUE, also your use of MAX will only ever take one value. 

You have now introduced Email into your measure, so there is no way I can solve this without some sample data, and all the other measures that may contribute to this. 

[powerbidev123 · Accepted solution]

Hi @mark_endicott ,  you are right on the part that max gives only 1 or 0 value. Let me reframe the question  a bit.  

Email	SkillName	Score
a	Amazon RDS	2
a	Automotive	1
d	Amazon RDS	1
d	Automotive	2
g	Amazon RDS	2
g	Automotive	2

Now I have put a slicer on SkillName in a separate query which gives distinct names

Dax used:  DISTINCT('Query1'[SkillName])

this above column I am using it in the AND operation measure in my main  query where the email, skillname,score reside which is as below:

Measure =

IF (

    ISFILTERED ( SkillsSelected[SkillName])  

        && MAX ( Query1[SkillName] )

            IN ALLSELECTED ( SkillsSelected[SkillName] )

                && COUNTROWS ( SkillsSelected)

                    <= CALCULATE (

                        COUNT ( Query1[SkillName] ),

                        FILTER (

                            ALLSELECTED ( Query1),

                            [SkillName]

                                IN ALLSELECTED ( SkillsSelected[SkillName] )

                                    && [Email] = MAX ( Query1[Email] )

                        )

                    ),

    1

)

Now the issue is the AND operation is working fine upon selecting from slicer but I need to display the count of the unique email in a separate card visual which I am unable to do as the measure gives only value as 1. The count in card visual that I need is 3 since 3 such unique emails are there which have both the Skills. 

[mark_endicott]

@powerbidev123 I'm sorry it is still completely unclear why you need the calculated table for SkillName, and cannot use the SkillName column from your Query1 table, then simply do:

DISTINCTCOUNT(Query1[Email])

Can you explain why this didnt work? it is not clear from your sample data. 

[powerbidev123 · Accepted solution]

@mark_endicott , used the separate calculated table to store the distinct skill name. so that it can be used in the 'AND' created measure . 

You are right that we can do it without even creating a calculated table but the main issue is how to get that measure to reflect in the count card

[mark_endicott]

@powerbidev123 - So your use of  MAX ( Query1[SkillName] ) is the problem, because it will only ever find one value in this table, which when unfiltered in the data you have supplied is "Automotive". If you were to apply a filter to this table, that value will change, but it will only ever be the last value alphabetically.

So, your DAX will show 1 when Automotive is selected, (blank) when Amazon RDS is selected and 1 if both are - if you replace the 1 with DISTINCTCOUNT(Query1[Email]) you will see 3 instead of 1, but still (blank) when Amazon RDS is selected in the slicer because it is not the MAX value. 

The way you are trying to do this, you cannot specify multiple values so replacing MAX with VALUES will not work. 

I think this is trying to be too complex, and I'm not sure why you need to be.