How to show Personal ID only having value 1 in a matrix table in row filed inside %days ofc in row?

[sagarsahoo_123]

Hi Team,

Hope all are doing good. I need your support to resolve one issue which i come across while presenting the matrix table.

In my matrix table in rows i have two value like "%Days in Office" and "Personal_ID". While expanding the %Days in office all Personal ID in each category is shown up, but i need to show only those which is having Head Counts as 1 or WFO Avg ACT is having some value. Please see how it comes while exapanding below.

In the above under >90% category all Personal ID is coming up, but i want to show only those having Total HC value as 1 in those category like 

To achieve this i have written a measure and used it instead of Personal ID in rows, but this measure itself is not dragged to the rows.

------

FilteredPersonalIDs =

CALCULATE(

    VALUES(VW_ARC_SKILL[Personal_ID]),

    FILTER(

        ALLEXCEPT(VW_ARC_SKILL, VW_ARC_SKILL[WFO_Category]),

        COUNTROWS(FILTER(VW_ARC_SKILL, VW_ARC_SKILL[Physical_Act_hrs] <> BLANK())) > 0

    )

)

------

but the above measure i can't use instead of Personal ID. Can somebody please help how can i achieve this to show only those Personal ID in each category of %Days coming to Office which is having either Total HC value as 1 or WFO Avg ACT is having some value.

Please note  "Head counts" is a measure of summing of total Personal ID used in Value filed of matrix.

Any help on this is highly appreciated.

Regards,

Sagar

[sagarsahoo_123]

@Angith_Nair 

Thanks a lot for such a prompt response. It really helped and solved the problem.

Thanks a lot again.

Regards,

Sagar

[Angith_Nair · Accepted solution]

Hi @sagarsahoo_123 

To filter the Personal_ID values in each %Days in Office category so that only IDs with a Head Counts measure equal to 1 or with non-blank WFO Avg ACT values are shown, you’ll need to use a measure that checks these conditions. Here’s a step-by-step approach:

1. Create a Filter Measure:

This measure checks if Head Counts is 1 or if WFO Avg ACT has a value. You can use this measure to dynamically filter Personal_ID values in the matrix.

DAX

ShowPersonalID = 
IF(
    [Head Counts] = 1 || NOT(ISBLANK([WFO Avg ACT])),
    1,
    0
)

2. Filter Using the Measure:

With the ShowPersonalID measure created, you can use it as a visual-level filter in your matrix to filter out any Personal_ID rows where the measure evaluates to 0.

> Go to your matrix visual, then in the Filters pane, apply a filter to ShowPersonalID and set it to show only values of 1.
> This will ensure that only Personal_IDs meeting the conditions appear in each %Days in Office category.

3. Alternative - Measure for Dynamic Row Display:

If you still need to display specific rows in the matrix directly without relying on Personal_ID in the row field, you could create another measure that pulls only those IDs satisfying the conditions:

DAX

FilteredPersonalIDs = 
CONCATENATEX(
    FILTER(
        VW_ARC_SKILL,
        [Head Counts] = 1 || NOT(ISBLANK(VW_ARC_SKILL[WFO Avg ACT]))
    ),
    VW_ARC_SKILL[Personal_ID],
    ", "
)

Use FilteredPersonalIDs in a card or matrix visual if you need to see these values together without adding Personal_ID as a row in the matrix.