Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone!
I'm trying to make a distinct count on a field on two tables that are not related. I'm making a distinct count and as a result, I have mesure1 for table1 and measure2 for table2 where if I group by customer I got a 1 for every customer in each table. Some cases measure1=1, measure2=0; measure1=0, and measure2=1 both cases it's ok because If I add the measures I get what I want (result=1). The problem is when I got measure1=1 and measure2=1 I add and get a 2 (I will be counting June customer twice)
I made a third measure
Try something along the lines of:
Measure =
COUNTROWS(
DISTINCT(
UNION(
SELECTCOLUMNS('Table1',"Column",[ColumnFromTable1]),
SELECTCOLUMNS('Table2',"Column",[ColumnFromTable2])
)
)
)
Try to create another Tabel using Summarise function so that you can have the columns in one table with respective values so that you can use single measure (distinctcount) to get the result the way you want
Proud to be a Super User!
Hi thx for your reply
I used summarized and it worked perfectly.
Thank you very much!
Best regards,
Mijalis
@Anonymous
IF(Total=2;Total/2;Total)
this always will give 1 based on you data sample and Im pretty sure 74 is a good sum for your data
how is your data look like in original data source? what result do you expect?
Hi, thx for the quickanswer.
I want 74 as a result, the problem is that power bi is giving 111, wich is the sum of total measure1 + total measure2. 74 will be the sum of the total column.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |