PowerBi calculated measures are not effected by categories

0
votes

I am new to powerBI and i have created a measure using two tables as given below.

Paid Users = 
VAR current_month = MAX('Date'[Dates])
VAR customers = CALCULATE(sum('users'[CustomersOrNot]), FILTER(ALL('users'),'users'[CustomersOrNotDate] <= current_month && 'users'[CanceledDate] > current_month && 'users'[category] IN {"001","002","003"}))
return customers

so we have users table and Date table but when i try to create a chart of paid Users with category i am getting same values for all the categories instead of different values for different categories. Users Table enter image description here Dates enter image description here

1
powerbidaxpowerbi-desktop
Sample data and desired output would be helpful – CR7SMS
You are using the FILTER(ALL(...)...) syntax which removes all the filters on the users table and later on introduce a filter on users by category in 001, 002, 003. Remove the ALL() and you should get your result: ALCULATE(sum('users'[CustomersOrNot]), FILTER('users',...... – user12493039
@FabianSchenker By removing ALL from the Filter i am getting quite different numbers that does not make sense. – jarry jafery
in that case, we would need more information about your data model and as described by cr7sms sample data and desired output. – user12493039
@CR7SMS my date table is quite simple like date, year, month etc and for the other i am updating the question. – jarry jafery

1 Answers

1
votes

In the calculation you are using Filter All which is creating issues. To work around this you can create a column with the following calculation:

Paid Users = 
VAR current_month = MAX('Date'[Dates])
VAR customers = CALCULATE(sum(users[CustomerOrNot]), FILTER(ALLEXCEPT('users',users[category]),'users'[CustomerOrNotDate] <= current_month && users[CanceledDate] > current_month && users[category] IN {"001","002","003"}))
return customers

You can now aggregate this using min(Paid Users)to get the results at a category level. Hope this helps.