Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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.
@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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
85 | |
82 | |
66 | |
49 |
User | Count |
---|---|
137 | |
111 | |
101 | |
66 | |
65 |