Forum Discussion

Mercovito's avatar
Mercovito
Frequent Visitor
2 years ago
Solved

Calculated Table with custom filtering

Hello Masters

I am trying to filter my table with DAX and create a new table.

I want to exclude companies from the main table those all factories are Inactive. Below picture; "Company C" should be excluded as all factories of Company C is Inactive. So new table will only have Company A, B and C

Can you please help?

Here, you can see the PBIX file.

https://drive.google.com/file/d/1zxp-Ln_e3Mzmzkefe68h5OWO76kQY2EH/view?usp=sharing

 

 

4 Replies

  • ThxAlot's avatar
    ThxAlot
    Super User
    2 years ago

     

    Table =
FILTER(
    DISTINCT( Sheet1[Company] ),
    NOT ISEMPTY(
        CALCULATETABLE( Sheet1, Sheet1[Factory Active - Inactive] = "Active" )
    )
)

     

  • FreemanZ's avatar
    FreemanZ
    Super User
    2 years ago

    hi Mercovito ,

    Not sure if i fully get you, try to create a calculated table like:

    Table = 
CALCULATETABLE(
    sheet1,
    FILTER(
        VALUES(sheet1[Company]),
        "Active" IN  CALCULATETABLE(VALUES(sheet1[Factory Active - Inactive]))
    )
)

    it worked like:

     

    • Mercovito's avatar
      Mercovito
      Frequent Visitor
      2 years ago

      Thank you so much; I need to burn my brain to understand this step by step 🙂