Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
User_ID | Type |
1 | Free |
1 | Paid |
2 | Paid |
2 | Free |
1 | paid |
3 | free |
4 | paid |
I am using above data to count number of only free users, number of only paid users and number of paid and free combine users. I want to create a measure. Like is in above example data we have 1 free user and 1 paid user and 2 users are free as well as paid. i want to show these numbers in donut chart
Thanks in advanced.
Solved! Go to Solution.
Hi @adnanarain ,
Please create two new measures:
free =
CALCULATE (
DISTINCTCOUNT ( 'Table'[User_ID] ),
FILTER (
'Table',
'Table'[Type] = "free"
&& 'Table'[User_ID] = MAX ( 'Table'[User_ID] )
)
)
paid =
CALCULATE (
DISTINCTCOUNT ( 'Table'[User_ID] ),
FILTER (
'Table',
'Table'[Type] = "paid"
&& 'Table'[User_ID] = MAX ( 'Table'[User_ID] )
)
)
Then create a new table:
Table 2 =
SUMMARIZE (
'Table',
'Table'[User_ID],
"status",
SWITCH (
TRUE (),
[free] <> BLANK ()
&& [paid] <> BLANK (), "paid and free",
[free] = BLANK ()
&& [paid] <> BLANK (), "paid",
[free] <> BLANK ()
&& [paid] = BLANK (), "free"
)
)
Finally, drag the fields into the donut chart:
The PBIX file is attached for reference.
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Hi @adnanarain ,
Please create two new measures:
free =
CALCULATE (
DISTINCTCOUNT ( 'Table'[User_ID] ),
FILTER (
'Table',
'Table'[Type] = "free"
&& 'Table'[User_ID] = MAX ( 'Table'[User_ID] )
)
)
paid =
CALCULATE (
DISTINCTCOUNT ( 'Table'[User_ID] ),
FILTER (
'Table',
'Table'[Type] = "paid"
&& 'Table'[User_ID] = MAX ( 'Table'[User_ID] )
)
)
Then create a new table:
Table 2 =
SUMMARIZE (
'Table',
'Table'[User_ID],
"status",
SWITCH (
TRUE (),
[free] <> BLANK ()
&& [paid] <> BLANK (), "paid and free",
[free] = BLANK ()
&& [paid] <> BLANK (), "paid",
[free] <> BLANK ()
&& [paid] = BLANK (), "free"
)
)
Finally, drag the fields into the donut chart:
The PBIX file is attached for reference.
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
how about if we use intersect will this be faster than this?
Hi @adnanarain ,
Should be faster than using INTERSECT().
you might consider using Dax studio to execute the command to compare the operation time.
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
with my data of 600K rows it is working too slow. created measures but with table it takes ages.
paid = calculate(distinctcount(user_id), type = "paid")
free= calculate(distinctcount(user_id), type = "free")
combined = paid + free
Proud to be a Super User!
i used below measure which is calulating correct but when i use this measure in another measure with sumx it takes ages to load data.
@vanessafvg wrote:paid = calculate(distinctcount(user_id), type = "paid")
free= calculate(distinctcount(user_id), type = "free")
combined = paid + free
@vanessafvg wrote:paid = calculate(distinctcount(user_id), type = "paid")
free= calculate(distinctcount(user_id), type = "free")
combined = paid + free
Measure 1
Paid_Free_Users =
var count_Free = CALCULATE(DISTINCTCOUNT(transactions[patientId]),FILTER(transactions,transactions[Type_Final]="Free" || transactions[Type_Final] = "First Free"))
var count_Paid = CALCULATE(DISTINCTCOUNT(transactions[patientId]),FILTER(transactions,transactions[Type_Final]="Paid"))
return
IF(count_Free>0 && count_Paid>0, 1, 0)
Measure 2:
Paid_Free_User_Count = sumx('transactions', [Paid_Free_Users])
using distinct count should still work because you have a filter on free or paid so it will still pick up both occurences of that user.
sumx is an iterator and will loop through the data row by row. the issue of it taking long can also be your model and how its set up or the volumes you have.
Proud to be a Super User!
Model is very simple just one main table and date table. 600k rows in the main table.
Yes but the if condition with and will compare both the occurance and do you have another solution for this??
yes so you trying to answer if a patient is both free and paid right?
i will have a look at this a bit later if there is a better solution, what i am trying to solve though the performance ? is the measure giving you the correct result. also please post a screenshot of your data model and the visual you are using to bring back the result.
Proud to be a Super User!
Yes exactly performance and measure is giving correct result i checked this with matrix visual by bringing type, patient id and added this measure in matrix filter and set the filter to greater than 0. I will post the model
did you manage to solve this, please provide a screenshot of your model.
Proud to be a Super User!
Thanks for your reply, I know this measure but this will not work as same user can be in both categories so i want to count users who are just in free category and also want to count users those are only in paid category and same goes for combine users.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
117 | |
109 | |
106 | |
92 | |
68 |
User | Count |
---|---|
167 | |
130 | |
129 | |
95 | |
91 |