ALLEXCEPT() behaves differently in Power BI and SSAS

[DroiD03]

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

[v-xuding-msft · Accepted solution]

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.

[DroiD03]

Thanks @v-xuding-msft ,

below thing worked for me.

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 !!