Forum Discussion

bolabuga's avatar
bolabuga
Icon for Helper V rankHelper V
9 years ago
Solved

[ Help with filters and comparisons ]

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

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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's avatar
      bolabuga
      Icon for Helper V rankHelper V
      9 years ago

      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.

    • bolabuga's avatar
      bolabuga
      Icon for Helper V rankHelper V
      9 years ago

      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.