cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DroiD03 Frequent Visitor
Frequent Visitor

ALLEXCEPT() behaves differently in Power BI and SSAS

Hi all,

 

Don't know the exact forum area to post this question so dropping bomb here!

 

ALLEXCEPT() in Power BI and SSAS behaves differently. Not sure others are facing this thing.

 

Suppose I have a column 'Budget' in Table 'T'(having relation with 1 dimension table 'Dim') and when I type a code like this-:

 

Tea = Calculate(SUM(Budget), ALLEXCEPT('T', 'Dim'[Col1]))

 

Works smooth in Power BI i.e. Budget amount will only get filtered with 'Col1' of 'Dim' table.

 

But When I try to write same code in SSAS , i mean creating measure in Tabular model-:

 

Tea:= Calculate(SUM(Budget), ALLEXCEPT('T', ...........)

 

It does not get the scope of the related 'Dim' table. 

 

Can someone help in this scenario. 

 

Regards,

Mukul

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: ALLEXCEPT() behaves differently in Power BI and SSAS

Hi @DroiD03 ,

In theory, the function works the same in SSAS. I think maybe it is caused by the relationships of the tables.

There are two blogs about the function of ALLEXCEPT that you can reference.

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

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

 

Best Regards,

Xue Ding

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

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: ALLEXCEPT() behaves differently in Power BI and SSAS

Hi @DroiD03 ,

In theory, the function works the same in SSAS. I think maybe it is caused by the relationships of the tables.

There are two blogs about the function of ALLEXCEPT that you can reference.

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

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

 

Best Regards,

Xue Ding

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

View solution in original post

Highlighted
DroiD03 Frequent Visitor
Frequent Visitor

Re: ALLEXCEPT() behaves differently in Power BI and SSAS

Thanks @v-xuding-msft ,

below thing worked for me.
1.PNG

 

 

 

 

 

 

 

 

But still in Power BI, i am getting the option to enter columns of related tables also. In SSAS, I can only enter Table names here.
Anyhow this also suffice my requirement.

Thanks !!

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 1,083 guests
Please welcome our newest community members: