restrict data in a column based on conditions

[Anonymous]

Hi,

How can i restrict to view only Employee Data, which does not have Null or 0.

In the below example i would need is only EMP ID  2 & 4 which does not have Null or 0

EMP ID     Material     Product id
1               A                Null
1               B                123
1               C                345

2               A               123
2               B                345

3               A               0
3               B               345

4               A              123
4               B              345
4               C              567
4               D             789

Regards,

San

[v-yuta-msft · Accepted solution]

@Anonymous ,

I suppose the Product id column is text type, you can create a calculate column using dax below:

Flag = CALCULATE(COUNTROWS('Table'), FILTER(ALLEXCEPT('Table', 'Table'[EMP]), 'Table'[Product id] <> "Null" && 'Table'[Product id] <> "0")) = CALCULATE(COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[EMP]))

 

Then create a calculate tabel using dax below to achieve the filtered table.

Filtered Table = SUMMARIZE(FILTER('Table', 'Table'[Flag] = TRUE()), 'Table'[EMP], 'Table'[Material], 'Table'[Product id])

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.