topic Re: Power BI Remove rows based on multiple conditions for dashboard in DAX Commands and Tips

Power BI Remove rows based on multiple conditions for dashboard

Anonymous — Wed, 15 Dec 2021 16:26:52 GMT

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

Re: Power BI Remove rows based on multiple conditions for dashboard

smpa01 — Wed, 15 Dec 2021 17:38:27 GMT

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

Re: Power BI Remove rows based on multiple conditions for dashboard

v-luwang-msft — Mon, 20 Dec 2021 08:53:03 GMT

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

Re: Power BI Remove rows based on multiple conditions for dashboard

Kate1053 — Wed, 21 Aug 2024 17:55:26 GMT

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.