Forum Discussion

JustinDoh1's avatar
JustinDoh1
Post Prodigy
5 years ago
Solved

My calculation does not work using variable (row context issue or something else?)

I am sharing my Pbix file here.       I posted a similar question yesterday, but this is little bit different (because I am trying to see whether somehow my logic has an error: ex "vNotElig...
Help with DAX
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    How about this for the variable:

     

    Var vNotEligibleStep1 = 
    COUNTROWS(
        FILTER(
            Data,
            Data[Consent] = "Not Eligible"  &&
            Data[Step] = 1
        )
    )

     

     

    This will return 1 for each row which meets the filtered criteria. You can now keep the switch function clause.

  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    Alternatively you can try:

    new measure = SUMX(Data, [Step 1_0819])

PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

The problem appears to be the variable Step1.

try:

CALCULATE(

DISTINCTCOUN(Data[ClientID]),

FILTER(Data,

Data[Step] = 1 && Data[Consent] <> "Refused" && NOT(ISBLANK(Data[Consent]))))

PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

Alternatively you can try:

new measure = SUMX(Data, [Step 1_0819])

  • JustinDoh1's avatar
    JustinDoh1
    Post Prodigy
    5 years ago

    PaulDBrown Actually, SUMX made more output of rows, so I think it might not be the solution, but I need to review data more. Thank you so much for all your help.

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    Can you post the updated PBIX file? Maybe we can take it step by step. 

  • JustinDoh1's avatar
    JustinDoh1
    Post Prodigy
    5 years ago

    PaulDBrown here is a link to the updated Pbix file. I guess I am leaning toward using SUMX (on this particular small data). 

    But, when I use other larger datasets (for other column, but with same approach (using Countrows --> SUMX), instead of showing 1, it shows as 2 occurrences. I guess there are duplicate occurrences with same condition. What should I do?  How can I make the dinstinct (SUMX)?

     

     

     

  • JustinDoh1's avatar
    JustinDoh1
    Post Prodigy
    5 years ago

    PaulDBrown I have created a new question regards to SUMX. I am really struggling to find the solution for getting Total on the bottom of Matrix because if use SUMX, it creates multiple output (rather than 1), but when I use SUMX, Total comes out to be "" (when I have to exclude anything).