Distinct Count based on multiple column

[Krunalbpatel]

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.

[BhaveshPatel]

Hi Krunal,

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

Regards

Bhavesh

[Krunalbpatel]

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

[v-yuezhe-msft · Accepted solution]

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

[Krunalbpatel]

Hi Lydia Zhang,

Thank you for your reply. I got my solution and its works fine for me.

Thanks and Regards,

Krunal Patel

[cosborn1231]

Hi Krunalbpatel,

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

I hope this helps,

Christian

[Krunalbpatel]

Hi cosborn1231,

No This will not help me. In this case I got the sum of distinct values but I want the distinct value count against value 1.

Thanks and Regards,

Krunal Patel