DAX 'EXCEPT' FUNCTION NOT WORKINT

[Anonymous]

Hi there,

I have  table A  called 'CIA_List_Impacted Team'. It has only one column called 'Impacted Team'.

When a slicer is applied to the column 'Impacted Team' in Table A, I want to create a new table B (to show the list of values not selected by the slicer) using the following DAX :

NOT impacted team = EXCEPT(ALL('CIA_List_Impacted Team'),'CIA_List_Impacted Team')

The above formula is not working and Table B is not showing any rows even when the slicer is used. There is no relationship between Table A and Table B.

Any help / advice will be much appreciated.

Thanks.

Iris

[OwenAuger · Accepted solution]

Hi @Anonymous 

Calculated tables (and calculated columns) are computed during report refresh, and are then static, i.e. don't change based on interaction with the report.

(See this article for discussion of creating "table functions" using DETAILROWS in Analysis Services but not Power BI)

You could use the EXCEPT(...) expression within a measure however, to act as a filter for some calculation for example. But you would have to repeat the expression within every measure that depended on it.

Regards,

Owen

[Anonymous]

Hi Owen,

Is there any way I can have Table B, using the current functionality in Power BI? Thanks.

[OwenAuger]

Hi Iris,

What behaviour are you trying to produce exactly? 

There is no way to have Table B as a physical table update based on a slicer selection or other filter in the report.

However you can have a table created using DAX code within a measure update based on slicers.

[Anonymous]

Hi Owen,

It's ok  - I've decided not to pursue down this path.  Thanks heaps for your help.

Iris

[Anonymous]

Thanks Owen.

[Anonymous]

Hi,

Can you provide us some sample data. 

And what does your slicer has ?

Thanks,

Tejaswi