Filtering help

[SDigital]

Hello

So i'm doing some analysis on my company's clients and some of them have projects with multiple sectors within our company.

I want to filter my data to show clients who have projects with one particular sector.

Thanks in advance.

[v-yulgu-msft · Accepted solution]

Hi @SDigital,

Based on my understanding, you want to display those clients who have only one project with only one sector, right?

If so, you can create a measure similar to below, add this measure to visual level filter and set its value to 1.

check1 =
IF (
    CALCULATE (
        DISTINCTCOUNT ( Sheet1[Project] ),
        ALLEXCEPT ( Sheet1, Sheet1[Client] )
    )
        = 1
        && CALCULATE (
            DISTINCTCOUNT ( Sheet1[Sector] ),
            ALLEXCEPT ( Sheet1, Sheet1[Client], Sheet1[Project] )
        )
            = 1,
    1,
    0
)

Best regards,

Yuliana Gu

[SDigital]

Hi @v-yulgu-msft

Thanks for answering my query. This formula is almost there but i want to show clients that have one or more projects with one sector.

Thanks again!