Forum Discussion

Rabur116's avatar
Rabur116
Frequent Visitor
6 years ago
Solved

Distinct Count of Object Per Type and Date

Hi,

 

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

Object NameObject TypeDate
abcvm1ahv3/26/2020
abcvm2vmw3/26/2020
bcdvm1ahv3/25/2020
bcdvm1ahv3/25/2020
bcdvm1ahv3/24/2020
bcdvm1vmw3/24/2020
cdwvm1ahv3/24/2020

 

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

DateahvvmwTotal
3/26/2020112
3/25/2020101
3/24/2020213
  Grand Total6

 

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!

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    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.

6 Replies

    • Rabur116's avatar
      Rabur116
      Frequent Visitor
      6 years ago

      Your solution works! Thanks a lot for the help.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Rabur116 

    Try like

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

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Rabur116
      Frequent Visitor
      6 years ago

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