Help with differentiating individuals and associated conditions within the month

[JDobski]

Hello All,

This is my first time posting so please bear with me!

I'm having some issue with a calculation as more people are entering our datatset.

I have a graph that is calculating individuals across two distinct categories, and what I'm finding is happening is that if an individual is passing through both categories within the month, they are counting them within both categories, when what I need is to count whatever category an individual is in at the end of the month, thereby, only counting them in one category per month.

This is the calculation:

RunningSum  Category A=
CALCULATE(
SUM(Category A ,
FILTER(
ALLEXCEPT(several filtered factors ),
table[LastDayOfMonth] <= MAX(table[LastDayOfMonth])
))

RunningSum  Category B=
CALCULATE(
SUM(Category B ,
FILTER(
ALLEXCEPT(several filtered factors ),
table[LastDayOfMonth] <= MAX(table[LastDayOfMonth])
))

Any help you could provide would be greatly appreciated!

🙂

Jess

[Anonymous · Accepted solution]

@JDobski 

You cannot just allexcept like that in dax, try measures like the following.

RunningSum  Category A=
CALCULATE(
SUM([value] ,
FILTER(Allselected(table), table[Category]="A" &&
table[LastDayOfMonth] <= MAX(table[LastDayOfMonth])
))

RunningSum  Category B=
CALCULATE(
SUM(Category B ,
FILTER(Allselected(table), table[Category]="B" &&
table[LastDayOfMonth] <= MAX(table[LastDayOfMonth])
))

However, without seeing the data, it is difficult to provide further detail. You can repost a sample pbix and replace sensitive data with random values.

Paul Zheng _ Community Support Team

[JDobski]

Hello,

Thanks for taking the time to answer, unfortunately I'm not able to share the dataset which is a bit of a bummer. I've looked on the desktop forums and I'll certainly post my question there next time.

Unfortunately the distinct count doesn't quite work for the above, I've tried to assign the two calculations as variables but it's throwing a syntax error. I'll see what I can find on the forums!

Thanks again.

[negi007]

@JDobski not sure how your data looks like. There is a disinctcount function in dax which you can use to count distinct values in your data. if that does not help, please share your sample dataset so that we can assist you. Also this is not related to powerquery but with PowerBi desktop so it took longer than expected time to reply by someone.