Distinct Count of Object Per Type and Date

[Rabur116]

Hi,

I have this table where I need to count the Grand Total number of object by date:

Object Name	Object Type	Date
abcvm1	ahv	3/26/2020
abcvm2	vmw	3/26/2020
bcdvm1	ahv	3/25/2020
bcdvm1	ahv	3/25/2020
bcdvm1	ahv	3/24/2020
bcdvm1	vmw	3/24/2020
cdwvm1	ahv	3/24/2020

An object should be counted only once per day if it's of the same object type. The answer should be:

Date	ahv	vmw	Total
3/26/2020	1	1	2
3/25/2020	1	0	1
3/24/2020	2	1	3
		Grand Total	6

I am using a "Card" visualization to show the Grand Total as I filter the date/s. Please let me know the correct Measure that I can use for the "Card".

Thanks in advance!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-easonf-msft · Accepted solution]

Hi , @Rabur116 

Try steps as below:

1.create a calculate table:

Table 2 = DISTINCT('Table'[Object Type])

2.create a mesure as below

value1 =
SUMX (
    GROUPBY (
        FILTER ( 'Table', 'Table'[Object Type] IN DISTINCT ( 'Table 2'[Object Type] ) ),
        'Table'[Date],
        'Table'[Object Type]
    ),
    IF (
        ISBLANK ( CALCULATE ( DISTINCTCOUNT ( 'Table'[Object Name] ) ) ),
        0,
        CALCULATE ( DISTINCTCOUNT ( 'Table'[Object Name] ) )
    )
) + 0

It will show as below:

Here is  a demo

pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rabur116]

Hi, both the first person's solution and your solution work. Thanks a lot for the help!

[Ashish_Mathur]

You are welcome.

[amitchandak]

@Rabur116 

Try like

Sumx(summarize(Table[Date],table,[ Object Type]"_dist",distinct(Table[Object Name])),[_dist])

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Rabur116]

Your solution works! Thanks a lot for the help.