Measure to count free and paid users

[adnanarain]

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.

[v-cgao-msft · Accepted 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

[adnanarain]

how about if we use intersect will this be faster than this?

[v-cgao-msft]

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

[adnanarain]

with my data of 600K rows it is working too slow. created measures but with table it takes ages.

[vanessafvg]

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

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

combined = paid + free

[adnanarain]

---

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])

[vanessafvg]

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.  

[adnanarain]

Model is very simple just one main table and date table. 600k rows in the main table.

[adnanarain]

Yes but the if condition with and will compare both the occurance and do you have another solution for this??

[vanessafvg]

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.

[adnanarain]

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

[vanessafvg]

did you manage to solve this, please provide a screenshot of your model.

[adnanarain]

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.