Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Help with differentiating individuals and associated conditions within the month

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

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Anonymous 

    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

3 Replies

  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous 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. 

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Anonymous 

    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