Forum Discussion

ryanxngo's avatar
ryanxngo
New Member
2 years ago
Solved

FILTER using column values instead of a scalar value

I am trying to create a cumulative sum measure using the following formula:

Cumulative_Cash =
VAR CurrentRowDate = MAX(FactSales[CalendarDate])
VAR SelectedGrains = VALUES(FactSales[Grain])
RETURN CALCULATE(
    SUM(FactSales[Total_Cash]),
    FactSales[Grain] = SelectedGrains,
    FILTER(
        ALL(FactSales),
        FactSales[CalendarDate] <= CurrentRowDate
    )
)

Within the FILTER function, I have an additional condition for Grain:
FactSales[Grain] = SelectedGrains
If it is only one selection, it works but if I select two or more grains it gives error: A table of multiple values was supplied where a single value was expected.

Is there anyway to filter the FactSales[Grain] column to the values selected in the SelectedGrains table variable?



 

  • FreemanZ's avatar
    FreemanZ
    2 years ago

    hi ryanxngo ,

     

    try like:

    1) add a calculated column like:

     

    MonthNO = FORMAT([Date], "YYYYMM")

     

     2) plot a table visual with month column and a measure like:

     

    C_Cash = 
SUMX(
    FILTER(
        ALLSELECTED(data), 
        data[MonthNO]<=MAX(data[MonthNO])
    ),
    data[Cash]
)

     

    or 

     

    C_Cash2 = 
CALCULATE(
    SUM(data[Cash]),
    ALLSELECTED(data), 
    data[MonthNO]<=MAX(data[MonthNO])
)

     

     

    it worked like:

     

3 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi,

    Here is how to do this:

    Data:

    Dax:

    Cumulative_Cash =
    VAR CurrentRowDate = MAX(FactSales[Date])
    RETURN
    CALCULATE(
        SUM(FactSales[Total_Cash]),      
            ALL(FactSales[Date]),
            FactSales[Date] <= CurrentRowDate
        )

    End result:

    The calculation seems to work as expected considering the test data.

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

  • ryanxngo's avatar
    ryanxngo
    New Member
    2 years ago

    Hi ValtteriN,
    Thank you so much, I thought this had worked for me but seems like there's one more issue:
    I have a month columns based on Date so if I use the Month column the cumulative sum doesn't work as expected

     

    Here is my dataset:



    The formula that I used:

    C_Cash =
    VAR CurrentRowDate = MAX(FactSales[Date])
    RETURN
    CALCULATE(
        SUM(FactSales[Total_Cash]),      
            ALL(FactSales[Date]),
            FactSales[Date] <= CurrentRowDate
        )



    I hope you can help me with this as well, thanks

    • FreemanZ's avatar
      FreemanZ
      Icon for Super User rankSuper User
      2 years ago

      hi ryanxngo ,

       

      try like:

      1) add a calculated column like:

       

      MonthNO = FORMAT([Date], "YYYYMM")

       

       2) plot a table visual with month column and a measure like:

       

      C_Cash = 
SUMX(
    FILTER(
        ALLSELECTED(data), 
        data[MonthNO]<=MAX(data[MonthNO])
    ),
    data[Cash]
)

       

      or 

       

      C_Cash2 = 
CALCULATE(
    SUM(data[Cash]),
    ALLSELECTED(data), 
    data[MonthNO]<=MAX(data[MonthNO])
)

       

       

      it worked like: