Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
latheesh89
Helper II
Helper II

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

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

3 REPLIES 3
v-henryk-mstf
Community Support
Community Support

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
Super User
Super User

@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

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.