Forum Discussion

LaurensM03's avatar
LaurensM03
New Member
2 years ago

Filter a table based on measure with multiple OR's

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
Need Help

10 Replies

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

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

    and expected output!

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

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

    • Ahmedx's avatar
      Ahmedx
      Icon for Super User rankSuper User
      2 years ago

      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)

       

       

    • LaurensM03's avatar
      LaurensM03
      New Member
      2 years ago

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

       

  • Igna's avatar
    Igna
    Icon for Resolver III rankResolver III
    2 years ago

    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's avatar
      LaurensM03
      New Member
      2 years ago

      Hi Igna,

       

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