Forum Discussion
How to pass a column into filter() ?
I'm trying to calculate the last year sales dynamically based off of the dates selected on the slicer. Within my date dimension table, each calendar date has a corresponding last year date. I've created a measure to do that:
pbi_Net_Sales_Total_LY_old = CALCULATE(sum(Merch[net_amt]), filter(all('Merch'), Merch[trans_dt] = VALUES('Calendar'[CALENDAR_DT_LY])))
This works fine if only one value has been selected, as shown below.
When more than one value has been selected, it throws a multiple value error message.
My approach is capturing the last year dates that are based off of the dates selected on the slicer and passing them into the filter in my measure so that it will only sum the sales that has a transaction date that equals to the last year dates that I'm passing. Seems like "Merch[trans_dt] =" doesn't like mulitple values and VALUES('Calendar'[CALENDAR_DT_LY]) containing multiple values is not helping. Is there anyway I can make the filter accept multiple values, in my case a column that contains multiple values? Or, is my approach the correct way of doing this?
Here's an extremely simiplified version of what my data model looks like:
If this matters, the data model is actually in Analysis Services, not in Power Bi.
3 Replies
Hi Anonymous
I think the easiest way would be to have a relationship between Calendar[CALENDAR_DT_LY] and [trans_dt]. By doing that the slicer would filter Merch directly. Is that possible?
If you already have another active relationship between those two tables you can always creative an additional inactive one and activate it within the measure through USERELATIONSHIP.
- That wouldn't be possible because the entire dashboard revolves around looking at specific time period using [trans_dt], which is a many to one relationship to calendar[calendar_dt]. Also, end users will not be using last year dates as a selection criteria. The whole point of this measure is to calculate last year's sales dynamically without any user interaction.
Just need to figure out how to make the filter accept multiple values that are dynamic. Unless my approach is not the correct way of going about doing what I want.....
P.S. This would be so much easier in SQL.....
