Power BI Remove rows based on multiple conditions for dashboard

[Anonymous]

Hi all, 

I'm struggeling with the following issue:

I have a table which looks like this:

Country	sale status
US	allowed
GER	not allowed
AUT	allowed
GER	allowed

My goal is to loop through the country column and identify duplicates and at the same time the command should check in case of duplicated values for a second condition which is "not allowed" in the column sales status. If the both conditions are matched the "not allowed" row should be removed completely from the table.

I hope you guys can help me out 🙂

Regards,

Elena

[Kate1053]

This helped me get started, but the sample code was based on there being only one instance of "not allowed". Here is something that should be more flexible:

mark = 
VAR test =
    CALCULATE (
        COUNT ( 'Table'[sale status] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Country] = MAX ( 'Table'[Country] ) )
    )
RETURN
    IF (
        test >= 2
        && MAX ( 'Table'[sale status] ) = "not allowed",
        0,
        1
    )

Also, you don't necessarily need the separate table. Just filter your visual/page/dashboard by the measure field.

[v-luwang-msft · Accepted solution]

Hi  @Anonymous ,

Try steps like  below:

base on table ,create a measure:

mark = 
VAR test1 =
    CALCULATE (
        COUNT ( 'Table'[sale status] ),
        FILTER ( ALL ( 'Table' ), 'Table'[sale status] = "not allowed" )
    )
VAR test2 =
    CALCULATE (
        COUNT ( 'Table'[sale status] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Country] = MAX ( 'Table'[Country] ) )
    )
RETURN
    IF (
        test1 >= 1
            && test2 > test1
            && MAX ( 'Table'[sale status] ) = "not allowed",
        0,
        1
    )

Step 2, custom  create new table:

Table 2 = CALCULATETABLE('Table',FILTER('Table','Table'[mark]=1))

 Output result:

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

Best Regards

Lucien

[smpa01]

@Anonymous  can you please provide better dta; this dat does not seem to be reprentative of the issue you rae describing