Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Quick Measures not working in table visual

Hi,

 

I am testing quick measures for running totals. There is an issue, when I use the running total in a table visual, so that it doesn'n sum at all.

 

test table:


Index Date                          Type  Value

101.01.2019 00:00:00A100
203.01.2019 00:00:00B200
307.01.2019 00:00:00A300
402.02.2019 00:00:00B1040
502.02.2019 00:00:00B350
615.02.2019 00:00:00B230
705.03.2020 00:00:00A550

 

When I put all those columns into a table visual and add the quick measure running total:

 

Running Total =
CALCULATE(
    SUM('FactsTable'[Value]);
    FILTER(
        ALLSELECTED('FactsTable'[Index]);
        ISONORAFTER('FactsTable'[Index]; MAX('FactsTable'[Index]); DESC)
    )
)
 
the result is:
 
 
When I put that measur into a matrix, it works.
 
I think it is because of the ALLSELECTED function. When I put ALLSELECTED('FactsTable') instead of ALLSELECTED('FactsTable'[Index]) it works in the table visual.
 
Can someone explain this behavior?
 
thx
 
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous 

    This relates to the row context and filter context in DAX,

    https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

    https://docs.microsoft.com/en-us/dax/allselected-function-dax

    If you use this measure

    Running Total =
    CALCULATE(
        SUM('FactsTable'[Value]);
        FILTER(
            ALLSELECTED('FactsTable'[Index]);
            ISONORAFTER('FactsTable'[Index]; MAX('FactsTable'[Index]); DESC)
        )
    )
    It only removes context filters for Index column.
    Now the visual should only incude Index attribute column. If there are other attribute column, it needs same.
    For example
    In your case, for the same date and type, it will work.
     
    So you need put ALLSELECTED('FactsTable') instead of ALLSELECTED('FactsTable'[Index]).
     

    Best Regards,

    Lin

1 Reply

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, Anonymous 

    This relates to the row context and filter context in DAX,

    https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

    https://docs.microsoft.com/en-us/dax/allselected-function-dax

    If you use this measure

    Running Total =
    CALCULATE(
        SUM('FactsTable'[Value]);
        FILTER(
            ALLSELECTED('FactsTable'[Index]);
            ISONORAFTER('FactsTable'[Index]; MAX('FactsTable'[Index]); DESC)
        )
    )
    It only removes context filters for Index column.
    Now the visual should only incude Index attribute column. If there are other attribute column, it needs same.
    For example
    In your case, for the same date and type, it will work.
     
    So you need put ALLSELECTED('FactsTable') instead of ALLSELECTED('FactsTable'[Index]).
     

    Best Regards,

    Lin