Forum Discussion
DAX expression to remove calculated filter
I have a calculated measure filter Flag_Week used to restrict table rows to 1 week only
Selected_Week = CALCULATE(SELECTEDVALUE('Weeks'[Fiscal_Week_Number],"Current"))
Flag_Week = IF([Selected_Week] IN VALUES(Main[Fiscal_Week_Number]), 1, 0)
For some measures I need to refer to all weeks:
e,.g. CALCULATE(SUM(Main[KPI_Tile_1_Value]),ALL(Main[Flag_Week]))
I have tried ALL etc but they only remove the restriction on actual table columns, not calculated measures
Any ideas please?
5 Replies
- Hi Venona,
How are you using this measure to filter out the values? Is this measure placed on the filter pane? Is this measure used with other measures?
If you are using it on the filter pane then you are not able to filter out that value I would do the contrary use the measure as a filter on the measure you want only as one week but in this case you need to use it on a filter sytax something similar to:
Measure = CALCULATE (SUM (TABLE[COLUMN]), FILTER(TABLE, [FLAG_WEEK] =1))
Regards,
MFelixThanks, but thats the opposite of what I need to do
The week filter is applied through a slicer on an unlinked table
There are other slicers as well for things like region, product etc
Using ALL with column name does not work as have to use measure
So, if filter is on Week 1 this would show:
Week Measure1 Measure2
Week1 Sum of Sales Sum of sales for all weeks 1-52(I am actually generating an SVG sparkline to go in the cell, which has x axis of all previous weeks)
- Hi Venona,
I understand that my solution is the contrary of what you are asking, what I intend to tell you is that sometimes the best option is not using the filter on the same way you usually would use, because depending on the context you may need to think in a different route.
In order to assist you better can you share some sample data and expected result?
Regards,
MFelix
Hi Venona ,
You may create relationship between the table 'Main' and 'Weeks' on field [Fiscal_Week_Number], and change the Cross filter direction from Single to Both , which will take these tables treated as a single table.
Then you can create measure to remove all filters like DAX below.
Measure2=CALCULATE(SUM(Main[KPI_Tile_1_Value]),ALL('Weeks'[Fiscal_Week_Number]))
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
