Filter a table based on measure with multiple OR's

[LaurensM03]

Dear all,

I am trying to filter a table based on the measure pasted below. A row from the table 'sheet1' should be shown in the table if:

• the column 'parent link' contains the string as defined AND
  • the column 'CRA intake status' equals "To Do" OR
  • the column 'Eligible for Capitalisation' equals "No" or is blank OR
  • the column 'Solution Intent status' equals "To Do" or "Undefined".
    

This code gives me the following error 'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.' and I don't know how to solve it. Could anyone help out?

Filter missing requirements =

VAR MTMB = "ABN-1095"

VAR Filtermissing =

    FILTER('Sheet1',

        CONTAINSSTRING('Sheet1'[Parent Link], MTMB) &&

        'Sheet1'[CRA Intake Status] = "To Do" ||

        'Sheet1'[Eligible for Capitalisation] = "No" ||

        ISBLANK('Sheet1'[Eligible for Capitalisation]) ||

        'Sheet1'[Solution Intent status] = "Undefined" ||

        'Sheet1'[Solution Intent status] = "To Do"

        )

RETURN

Filtermissing

[Igna]

Hi,

Can you try like this ?

Filter missing requirements =
VAR MTMB = "ABN-1095"
VAR Filtermissing =
    FILTER('Sheet1',
        CONTAINSSTRING('Sheet1'[Parent Link], MTMB) &&
        (
            'Sheet1'[CRA Intake Status] = "To Do" ||
            'Sheet1'[Eligible for Capitalisation] = "No" ||
            ISBLANK('Sheet1'[Eligible for Capitalisation]) ||
            'Sheet1'[Solution Intent status] = "Undefined" ||
            'Sheet1'[Solution Intent status] = "To Do"
        )
    )
RETURN
Filtermissing

Hope it helps

Igna

[LaurensM03]

Hi Igna,

Thanks, unfortunately I still receive an error. I attached the source files to the comment of Ahmedx

[Ahmedx]

but a measure cannot be a table, did you create a measure?

[Ahmedx]

write like this and select 1

Filter missing requirements =
VAR MTMB = "ABN-1095"
VAR Filtermissing =
    FILTER('Sheet1',
        CONTAINSSTRING('Sheet1'[Parent Link], MTMB) &&
        'Sheet1'[CRA Intake Status] = "To Do" ||
        'Sheet1'[Eligible for Capitalisation] = "No" ||
        ISBLANK('Sheet1'[Eligible for Capitalisation]) ||
        'Sheet1'[Solution Intent status] = "Undefined" ||
        'Sheet1'[Solution Intent status] = "To Do"
        )

RETURN
if ( COUNTROWS(Filtermissing)>1,1,0)

[thuoc115]

Thank you

[Ahmedx]

it was the right decision, if so then you will accept it as a decision?

[LaurensM03]

Hi, I created a measure to use as a filter in the filter pane applying it to a table visual.

[Ahmedx]

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

and expected output!

[LaurensM03]

In this dummy file I forgot to change the following in the above code:
The second line "ABN-1095" should be replaced with "Theme 1"

[LaurensM03]

I attached both files via a wetransfer link.https://we.tl/t-q6Uxg6F1SV