Forum Discussion

Ildoin's avatar
Ildoin
New Member
8 years ago
Solved

Exclude whole group if value is found

I am trying to calculate in Power BI values (Sum) for the Items that have only user specified value in column Code. In my case the the whole row should be excluded if the value is other than "X". For...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    8 years ago

    Hi Ildoin,

     

    To achieve your requirement, you can create a calculate column like below:

    IsValid Column = 
IF (
    CALCULATE (
        COUNTROWS(Table1),
        ALLEXCEPT ( Table1, Table1[Item] )
    )
        <> CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[Item], Table1[Code])),
    FALSE(),
    IF (
        CALCULATE (
            VALUES ( Table1[Code] ),
            ALLEXCEPT ( Table1, Table1[Item], Table1[Code] )
        )
            <> "X",
        FALSE(),
        TRUE ()
    )
)

     

    Then create a slicer based on IsValid Column, select True, you will achieve what you want.

     

     

    Hope it's helpful to you.

     

    Jimmy Tao

Gazzer's avatar
Gazzer
Icon for Resolver II rankResolver II
8 years ago

I don't know how to write the DAX for this (someone cleverer will come along soon, I'm sure) but I did this using built-in Power BI functions.

 

I duplicated your table and then filtered it to KEEP rows where the Code - 'Z'

Then I merged the first table with the new, filtered duplicate, using an 'anti' join type.

 

 

Lastly, I used Group By on the Item column and aggregated the Amount to get the desired outcome.