topic Re: FILTER using column values instead of a scalar value in DAX Commands and Tips

FILTER using column values instead of a scalar value

ryanxngo — Wed, 08 Nov 2023 15:53:36 GMT

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?

Re: FILTER using column values instead of a scalar value

ValtteriN — Wed, 08 Nov 2023 16:36:06 GMT

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/

Re: FILTER using column values instead of a scalar value

ryanxngo — Wed, 08 Nov 2023 17:49:18 GMT

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

Re: FILTER using column values instead of a scalar value

FreemanZ — Thu, 09 Nov 2023 02:58:41 GMT

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] )

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

it worked like: