Conditional filtering?

[AlexF_HH]

Hi Community,

i guess i have a beginner question here.
Please take a look at this simple table:

My taks is to qount the unique items in 'Column1' that equal X but NOT Y in 'Column 2'.
The result shoub be 'B' -> 1.

Normal Filtering doesn't work for that kind of problems.
The best formula i was able to build was:

Measure  = CALCULATE(DISTINCTCOUNT(Tabelle1[Column1]);Tabelle1[Column2] = "X" && Tabelle1[Column2] <> "Y")

But that formula returns A, B -> 2

Maybe you can suggest a nice solution for that.

It would be great if the logic also works with mutiple factors like: Count uniques items WHERE Column2 IS 'X' BUT NOT 'Y'  AND Column3 IS 'Z' BUT NOT 'W' etc..

Thank you!

Alex 

[v-deddai1-msft]

Hi @AlexF_HH ,

Would you please tell me if the screenshot below is your expected output? Or you can show us your expected output?

You can create a measure like:

Measure =

IF (

    "Y" IN VALUES ( 'Table'[Column2] ),

    0,

    CALCULATE (

        DISTINCTCOUNT ( 'Table'[Column1] ),

        FILTER ( 'Table', 'Table'[Column2] <> "Y" )

    )

)

If you want the logic also work with multiple factors, the measure really need to be modified depend on your requirement.

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

Best Regards,

Dedmon Dai

[AlexF_HH]

Hi @v-deddai1-msft ,

thank you for your feedback.

i just expected the total in your output to be 1.

B is the only item that has a Column2 relation = X and NO relations = Y.
So that part is correct: 

By multiple factors i mean the same kind of logic but used multiple times.

Count unique items in Column1 having Column 2 = X but NOT = Y AND Column3 = "yes" but NOT = "no".
The expecxted Measure  output would be B -> 1.

Thank you

Alex

[v-deddai1-msft · Accepted solution]

Hi @AlexF_HH ,

Using measure returns only one aggregate value, not one row. But you can add a calculated table for it or add a filter in table visual.

Here is the dax for calculated table:

Table 2 =

VAR a =

    SUMMARIZE (

        'Table1',

        'Table1'[Column1],

        "ifcontainY", IF (

            "Y" IN VALUES ( 'Table1'[Column2] ),

            0,

            CALCULATE (

                DISTINCTCOUNT ( 'Table1'[Column1] ),

                FILTER ( 'Table1', 'Table1'[Column2] <> "Y" )

            )

        )

    )

RETURN

    FILTER ( a, [ifcontainY] <> 0 )

>>Count unique items in Column1 having Column 2 = X but NOT = Y AND Column3 = "yes" but NOT = "no".

Please refer to:

Table =

VAR a =

    SUMMARIZE (

        'Table1',

        'Table1'[Column1],

        "ifcontainY", IF (

            "Y" IN VALUES ( 'Table1'[Column2] )

                && "no" IN VALUES ( 'Table1'[Column3] ),

            0,

            CALCULATE (

                DISTINCTCOUNT ( 'Table1'[Column1] ),

                FILTER ( 'Table1', 'Table1'[Column2] <> "Y" && Table1[Column3] <> "no" )

            )

        )

    )

RETURN

    FILTER ( a, [ifcontainY] <> 0 )

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

Best Regards,

Dedmon Dai

[AlexF_HH]

Hi @v-deddai1-msft ,

thank you very much!

Best Regards

Alex