[ Help with filters and comparisons ]

[bolabuga]

Hello everyone, i need help with the filter on the screenshot.

[v-yulgu-msft · Accepted solution]

Hi @bolabuga,

If I understanding your requirement correctly, your expect result is 1, right?

Below is my test, please take a reference.

Add one calculated column:

col1 =
IF (
    Sample[SERVICE] = "A"
        || Sample[SERVICE] = "B"
        || Sample[SERVICE] = "C"
        || Sample[SERVICE] = "D"
        || Sample[SERVICE] = "E",
    1,
    0
)

Create measures:

Count2 =
CALCULATE (
    SUM ( Sample[col1] ),
    ALLEXCEPT ( 'Sample', 'Sample'[MONTH], 'Sample'[CLIENT] )
)

FinalCount =
COUNTAX (
    FILTER (
        'Sample',
        [Count2] < 2
            && 'Sample'[COD] > 1
            && ( 'Sample'[SERVICE] = "A"
            || 'Sample'[SERVICE] = "B"
            || 'Sample'[SERVICE] = "C" )
    ),
    [Count2]
)

Best regards,
Yuliana Gu

[bolabuga]

Im made a litle table trying to replicate the results to see what happens. i still having troubles understanding all except. Althought i can see why the intended result is right.

[bolabuga]

@v-yulgu-msft

I did a little change to include client "6" in the count and the solution works, as shown on the screenshot.

Question about the solution!!!

Im having trouble understand allexcept, and why the proposed filter works.

trying to understanding in phases, please correct me where im missing.

1- col1 will have "1" for every possible elegible and not elegible client to be counted , for the services that i need to filter

2- [count2] --> what happens to the "sum col1" when you say ALLEXCEPT ( 'Sample'; 'Sample'[MONTH]; 'Sample'[CLIENT] )??

3- FILTER ('Sample';[Count2] < 2 && 'Sample'[COD] > 1--> every col1 row values are "1" or "0", do we need to use count2 here?? 

4- && ( 'Sample'[SERVICE] = "A" || 'Sample'[SERVICE] = "B" || 'Sample'[SERVICE] = "C" ) --> summing the results that i need, ok, but, in which phase services "D" and "E" was removed from the counting???

Heheheh, i think all those doubt are related to my lack of understanding of allexcept function.