Remove context filter "except" for a given table (all columns)

[latheesh89]

Hi Techies,

I am creating a measure something like below,

= CALCULATE(SUM('Internet Sales[Sales Amount]), ALLEXCEPT('DimDate', 'DimDate'[Month Year],'DimDate'[Year]........)

I wanted to slice 'Sales Amount' only by columns under DimDate table and any other dimension should not be sliced or filter should not work. I know ALLEXCEPT function can be used in this scenario but the problem is, I have created lots of calculated columns under DimDate and more or less all of them will be used in the reports. So do I need to include all the columns from DimDate in ALLEXCEPT function or is there a simpler way to include just a table instead of mentioning all the columns ?

Thanks,

Latheesh

[v-henryk-mstf]

Hi @latheesh89 ,

According to your question, you want to filter out other columns in the DimTable table that have an impact on the calculation structure through the ALLEXPECT function. My suggestion is: try the ALLSELECTED function. Its effect is the opposite of ALLEXPECT. In the ALLSELECTED function, you only need to add the columns you need to calculate. Even if there are many other columns in the DimTable table, they will not affect it. Can also simplify function expressions.

M = CALCULATE(SUM('Internet Sales[Sales Amount]), ALLSELECTED('DimDate', 'DimDate'[column])

For the use of this function, you can also refer to the following link: Power Bi - DAX - ALLSELECTED function - PowerBI Docs

If the problem is still not resolved, please provide detailed error information and let me know immediately. looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@latheesh89 , Allexcept will keep date filter. In case you want to remove date filter you can use removefilters or  all

CALCULATE(SUM('Internet Sales[Sales Amount]), removefilters('DimDate'))

refer

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

[latheesh89]

Thanks @amitchandak for your response.

No, I want to keep the DimDate filter and exclude other dimensions. As I mentioned my problem is I have many columns in DimDate that I will be using as slicer/filter in the report. So I'm finding a way to handle rather than adding alll the columns in ALLEXCEPT