Forum Discussion

latheesh89's avatar
latheesh89
Icon for Helper II rankHelper II
5 years ago

Remove context filter "except" for a given table (all columns)

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

Need Help

3 Replies

    • latheesh89's avatar
      latheesh89
      Icon for Helper II rankHelper II
      5 years ago

      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

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    5 years ago

    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.