Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX 'EXCEPT' FUNCTION NOT WORKINT

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
Need Help
  • OwenAuger's avatar
    OwenAuger
    7 years ago

    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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi,

     

    Can you provide us some sample data. 

     

    And what does your slicer has ?

     

    Thanks,

    Tejaswi

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks Owen.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Owen,

       

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

      • OwenAuger's avatar
        OwenAuger
        Icon for Super User rankSuper User
        7 years ago

        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.