Forum Discussion

Amitkr174's avatar
Amitkr174
Icon for Helper III rankHelper III
6 years ago

DAX Help to Restrict Filtering Column in a table

Hi,

 

I need a help on DAX, I want to filter the 'Net Available Hours' only based on the 'Resource Region' filter. The 'Billable hours' should not get filtered out. It should remain 112,462 only. Screenshot is below:-

12 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    So, you need to use ALLEXCEPT in that case. Something along the lines of:

     

    CALCULATE(SUM([Billable Hours]),ALLEXCEPT('Table'[Resource Region]))

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Replacre it with the exact dax

        Hi Amit,

        It will work if you calculate the measure in the MEASURE 3 itself ,It will not work if you call it from earlier created DAX Measure.

         

         

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Amitkr174 ,

     

    I believe you need a disconected table for it:

     

    Create a new table using the region values: Region = VALUES('Table'[REGION])

     

    Do not relate it with any table and use it as slicer.

     

    Create your measures:

    _1 =
    VAR _region = VALUES(Region[REGION])
    RETURN CALCULATE(SUM('Table'[VALUE 1]); 'Table'[REGION] IN _region)
     
    _2 = CALCULATE(SUM('Table'[VALUE 2]))
     
    Check this file: Download PBIX 
     

    If you consider it as a solution, please mark as a solution and kudos.

    Ricardo

    • Amitkr174's avatar
      Amitkr174
      Icon for Helper III rankHelper III
      6 years ago

      Thanks camargos88  for your reply!

      Issue is that I using direct query here and I don't have rights to modify a table.

       

      Is there any other way via which this can be resolved? 

      • camargos88's avatar
        camargos88
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Amitkr174 ,

         

        You don't need to change your source, just remove the relationship in Power BI.

        If you want, you can duplicate the region values as table and use this example.

         

        Did I answer your question? Mark my post as a solution!
        Ricardo