Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bolabuga
Helper V
Helper V

[ Help with filters and comparisons ]

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

 

CM.PNG

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

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]
)

1.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yulgu-msft
Employee
Employee

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]
)

1.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

Capture.PNG

@v-yulgu-msft

 

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

 

ALLEXCEPT.PNG

 

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.