Forum Discussion

burak's avatar
burak
Frequent Visitor
10 years ago
Solved

Sum of Distinct Count by column

Hello Everyone,

 

I am having trouble calculating sum of Distinct Count. Basically, I would like to calculate Distinct count regarding a different column. Here is the example:

 

Term                ID

Spring 15          1

Spring 15          2

Spring 15          1

Spring 16          1

Spring 16          1

Spring 16          3

Spring 17          4

Spring 17          1

 

My original formula is DISTINCTCOUNT(Table[ID]). But it gives a total number 4. However, I would like to calculate sum of distinct ID for every term. It should have,

-2 distinct count for Spring 15,

-2 distinct count for Spring 16

-2 distinct count for Spring 17.

 

So distinct count would be 6 instead of 4.

 

By the way, there is something to point out. I was eligible to get correct number with a complicated formula but my page should react when we filter with a slicer. When I have something like (="Spring 15"), it does not response when Spring 15 is unselected in slicer.

 

It is not as complicated as it is seen. To recap, distinct count should be calculated for every data in column "term".

 

Sorry for the long message. I hope I can solve my issue.

  • You could create a new column that concatenates Term and ID and then create a measure that does a DISTINCTCOUNT of that column. This measure should react appropriately to slicers and such. So:

     

    Column

    TermIDs =CONCATENATE([Term],[ID])

    Measure

    DistinctTermIDs = DISTINCTCOUNT([TermIDs])

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion

    You could create a new column that concatenates Term and ID and then create a measure that does a DISTINCTCOUNT of that column. This measure should react appropriately to slicers and such. So:

     

    Column

    TermIDs =CONCATENATE([Term],[ID])

    Measure

    DistinctTermIDs = DISTINCTCOUNT([TermIDs])
    • burak's avatar
      burak
      Frequent Visitor

      I am surprised how fast you replied.

       

      Thank you so much!

    • wharding's avatar
      wharding
      Regular Visitor

      So how would you filter out a distict value?

       

      So in the example above how would you say return the distinct values = SPRING15

       

  • I have a set of ids 

     

    1

    2

    3

    1

    1

    2

    4

    5

    5

     

    and i want the id and its count

    1 - 3 times

    2 - 2 times

    3 - 1 time

    4 - 1 time

    5 - 2 times

     

    and i have written a measure for the same

    Measure = CONCATENATE(DISTINCT(Activityx[Column1.AppId]), DISTINCTCOUNT(Activityx[Column1.AppId]))

     

     

    but i am unable to get the expected result. How can i achive that?

  • I have a set of ids 

     

    1

    2

    3

    1

    1

    2

    4

    5

    5

     

    and i want the id and its count

    1 - 3 times

    2 - 2 times

    3 - 1 time

    4 - 1 time

    5 - 2 times

     

    and i have written a measure for the same

    Measure = CONCATENATE(DISTINCT(Activityx[Column1.AppId]), DISTINCTCOUNT(Activityx[Column1.AppId]))

     

     

    but i am unable to get the expected result. How can i achive that?