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

itsme-stephanie's avatar
itsme-stephanie
Frequent Visitor
5 years ago

I'm facing almost the same problem, except that I want to exclude the whole group if the value X is found.

So if we would extend the intital example:

Item	Code	Amount
AA	X	1
AA	X	2
AA	Z	3
BB	X	4
BB	Y	5
BB	Z	6
BB	X	7
CC	X	8
CC	X	9
DD      Y       1
DD      Z       2
EE      Y       3
EE      Z       4
EE      Z       5

Table1

 

My ask would be how to get only a "true" for DD & EE:

 

Item	IsValidColumn	
AA      False
BB      False
CC      False
DD	True
EE      True

 

I've tried to adapt the calculated column example, but it didn't worked out.

Any hint appriciated.