Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Hide/Remove row based on criteria

I am working on a change management dashboard for my company, and I am trying to create an automated process to hide/remove rows once all of the tasks related to the change have a complete status AND the effective date has passed. I already have a measure written to assign a number to each of the status options if that helps. (Does Not Apply =1, Not Started=2,In Process=3, Complete=4)  I am relatively new to DAX, so any help would be greatly appreciated.  

Need Help
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    You shouldn't need to use VALUE with the measures.  That just converts strings to numerical values.  If the status number measure actually returns "4" instead of 4, you can add quotations around the comparison value to keep it simple.

     

    ECMFilter2 =
IF (
SELECTEDVALUE('Project1)'[EffectiveDate]) < TODAY() &&
[ME Status Number] = 4 &&
[QE Status Number] = 4 &&
[MR Status Number] = 4 &&
[PC Status Number] = 4 &&
[TL Status Number] = 4,
TRUE(),
FALSE()
)

    Nothing's changed for the date conditional.  Does it still return true in all instances?

15 Replies

  • Cmcmahan's avatar
    Cmcmahan
    Resident Rockstar
    7 years ago

    The easiest way to do this is to create a measure that returns True/False based on the conditions you want.

    HideProject = 
    IF (
        SELECTEDVALUE(Projects[EffectiveDate]) < NOW() && 
            SELECTEDVALUE(Projects[ME] = Complete) && 
            SELECTEDVALUE(Projects[QE] = Complete) && 
            SELECTEDVALUE(Projects[MR] = Complete) && 
            SELECTEDVALUE(Projects[PC] = Complete) && 
            SELECTEDVALUE(Projects[TL] = Complete), 
        TRUE(), 
        FALSE()
    )

    And then you can add this measure into a visual level filter, and hide results where this is true.  Depending on how your tables are set up, this may not be the exact syntax, but it should get you started in the right direction.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I keep receiving an error message that says a single value for column ME cannot be determined. How do I look at this row by row for each project?

       

      • Cmcmahan's avatar
        Cmcmahan
        Resident Rockstar
        7 years ago

        That would be becasue I forgot the closing parenthesis in part of the expression.  That's what I get for not testing this directly in PBI first.

         

        HideProject = 
IF (
    SELECTEDVALUE(Projects[EffectiveDate]) < NOW() && 
        SELECTEDVALUE(Projects[ME]) = Complete && 
        SELECTEDVALUE(Projects[QE]) = Complete && 
        SELECTEDVALUE(Projects[MR]) = Complete && 
        SELECTEDVALUE(Projects[PC]) = Complete && 
        SELECTEDVALUE(Projects[TL]) = Complete, 
    TRUE(), 
    FALSE()
)