Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
adnanarain
Helper V
Helper V

Measure to count free and paid users

User_IDType
1Free
1Paid
2Paid
2Free
1paid
3free
4paid
  

 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.

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

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:

vcgaomsft_0-1658718083150.png

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

View solution in original post

13 REPLIES 13
v-cgao-msft
Community Support
Community Support

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:

vcgaomsft_0-1658718083150.png

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.

vanessafvg
Super User
Super User

paid = calculate(distinctcount(user_id), type = "paid")

free= calculate(distinctcount(user_id), type = "free")

 

combined = paid + free





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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.  

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.