Forum Discussion

Krunalbpatel's avatar
Krunalbpatel
Helper III
9 years ago
Solved

Distinct Count based on multiple column

Hi,

 

I want to display value which is as described below.

My calculation is like this

 

var a =Distinct count of value 1

var b= Count of Value 2

var ans=b/a

 

I want to display ans as my result.

 

But Problem is there I have multiple records for all records. That means for value 1 (distinct) I got multiple values. So my calculation is not properly set as my requirement. So please help me in this case. If Found Unique count that is based on multiple column(value 1 and value 2) for var b.

 

Thanks.

 

 

 

 

  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Krunalbpatel,

    Please change the formula of var b to the following formula:

    var b = COUNTROWS(
 SUMMARIZE(Table,Table[Name],Table[Value1],Table[Value2])
)


    I create a table visual to display your expected result. For more details, you can review the example in this attached PBIX file:https://1drv.ms/u/s!AhsotbnGu1NogXByWhS2F551DemE.



    Thanks,
    Lydia Zhang

6 Replies

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    9 years ago

    Hi Krunal,

     

    Happy to help you with this. Please provide a snapshot of data and what are you trying to achieve.

     

    Regards

    Bhavesh

     

     

     

    • Krunalbpatel's avatar
      Krunalbpatel
      Helper III
      9 years ago

      Hi,

       

      I have data like this

      Id Name Value1 Value2

      1 abc      1          1

      2 abc      1          2

      3 abc      1          3

      4 pqr      2         1

      5 pqr      2         2

      6 abc      1          1

      7 abc      1          2

      8 mnq    3          1

      9 pqr      2         1

      10 xyz    4         2

       

      Now my calculation is like this

      var a = DISTINCTCOUNT(value1)

      var b = COUNT(Value2)

       

      My result wold give me like

      abc 1 - 5

      pqr  1 - 3

      mnq 1 - 1

      xyz   1 - 1

       

      But I need

      abc 1 - 3

      pqr  1 - 2

      mnq 1 - 1

      xyz   1 - 1

       

      For this I need to count distinct on multiple column so

      1 abc      1          1

      6 abc      1          1

      this will result only 1

       

      Thanks And Regards

      Krunal Patel

       

      • cosborn1231's avatar
        cosborn1231
        Resolver I
        9 years ago

        Hi Krunalbpatel,

         

        Test= SUMX(DISTINCT('Table'[Value2]),CALCULATE( VALUES('Table' [Value2])))

         

        I hope this helps,

        Christian