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.
Hey,
For one of my data models I'm finding a difference in the unique values within a table.
I want to know the number of unique customers that has a subscription and when this subscription is ending. A client can have multiple subscriptions. I want to know how many unique customers I have this year (table) and in which month the subscription is ending (graph).
I've made a table that has the customers IDs and the enddate of their subscription. I use a date table and a relationship with my fact table (subscriptions) to convert the specific end dates (i.e. 05-06-2021) to their respective months (May/Mei in my language). In the formatting tab I select to summarize by Values (distinct/unqiue).
If I add the values of each month the total is 1.602. And not the 1367 which the table summarizes?
https://pasteboard.co/JRO3Ra4.jpg
I'm finding differences in more slides now. How do I find the number of unique customers per month?
I've tried using a measure with DISTINCTCOUNT but ran into the exact same results...
Any ideas?
Solved! Go to Solution.
@Anonymous , The formula I suggested will force a total of rows.
Simple DISTINCTCOUNT (Table[customer id]), will be recalculated at grand total and subtotal level
@Anonymous
Try use Hasonevalue(), something like:
@Anonymous
Try use Hasonevalue(), something like:
@Anonymous , You have to create a measure like this
sumx(values(Table[Month year]), calculate(DISTINCTCOUNT (Table[customer id])))
Hi @amitchandak I've tried this solution and it seems right but now the total of the table are corresponding with the total of the values in each of the rows. Is this a normal result? And is there a work around for this?
@Anonymous , The formula I suggested will force a total of rows.
Simple DISTINCTCOUNT (Table[customer id]), will be recalculated at grand total and subtotal level
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 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |