Forum Discussion

BenJaamminn's avatar
BenJaamminn
Microsoft Employee
4 years ago
Solved

Hide rows based on multiple values in the same table

Hi everyone! First post to the community forum, so I'm terribly sorry if this is in the wrong section!

 

Currently I am building a porfolio management dashboard, the dashboard contains multiple tables (New Intake, Active Assessments, Committed Offerings, & Below the Line Offerings) that I would like items to be shown and hidden based on values in multiple columns. Simply put, hiding and showing a row based on multiple values.

 

Let me explain in further detail. I have a table showing the status of program assessments from different teams (PMO, CMO, CSBO, & MBO), and when each of their assessments are complete - green icon, I want to remove it from the Active Assessments table. And on a separate step place it in the Commited Offerings table but that can be solved another time. Image below;

I've tried "Filters on this visual" but it removes the entire row based on one filter, I'd love to base the removal on all assessments being complete. Oh! The icons are based on numbers 0-4, but I've used the switch function in DAX to do that. Originally its "Not Required, Not Started, In Progress, Complete, & Blocked", respective to 0-4.

 

I'm decent within Power Query and DAX, but not fluent so I would love any help you can offer! 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi BenJaamminn ,

     

    If your table looks like as below, please select columns and use Unipvot function in Power Query Editor to transform the table.

    New Table:

    Create a measure and use conditional formatting function.

    ICON = 
SWITCH(MAX('Active Assements'[Value]),"Blocked",0,"Not Required",1,"Not Started",2,"In Progress",3,"Complete",4)

    Then create a measure , add this measure into filter field in matrix and set it to show items when value =1.

    Filter Measure = 
VAR _CONDITION =
    CALCULATETABLE (
        VALUES ( 'Active Assements'[Value] ),
        ALLEXCEPT (
            'Active Assements',
            'Active Assements'[ID],
            'Active Assements'[Offering Title]
        )
    )
VAR _COUNT_CONDITION =
    CALCULATE (
        DISTINCTCOUNT ( 'Active Assements'[Value] ),
        ALLEXCEPT (
            'Active Assements',
            'Active Assements'[ID],
            'Active Assements'[Offering Title]
        )
    )
RETURN
    IF ( "Complete" IN _CONDITION && _COUNT_CONDITION = 1, 0, 1 )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi BenJaamminn ,

     

    If your table looks like as below, please select columns and use Unipvot function in Power Query Editor to transform the table.

    New Table:

    Create a measure and use conditional formatting function.

    ICON = 
SWITCH(MAX('Active Assements'[Value]),"Blocked",0,"Not Required",1,"Not Started",2,"In Progress",3,"Complete",4)

    Then create a measure , add this measure into filter field in matrix and set it to show items when value =1.

    Filter Measure = 
VAR _CONDITION =
    CALCULATETABLE (
        VALUES ( 'Active Assements'[Value] ),
        ALLEXCEPT (
            'Active Assements',
            'Active Assements'[ID],
            'Active Assements'[Offering Title]
        )
    )
VAR _COUNT_CONDITION =
    CALCULATE (
        DISTINCTCOUNT ( 'Active Assements'[Value] ),
        ALLEXCEPT (
            'Active Assements',
            'Active Assements'[ID],
            'Active Assements'[Offering Title]
        )
    )
RETURN
    IF ( "Complete" IN _CONDITION && _COUNT_CONDITION = 1, 0, 1 )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.