Forum Discussion

spandy34's avatar
spandy34
Icon for Responsive Resident rankResponsive Resident
4 years ago
Solved

DAX FILTER

Hi  I have the following measure which returns 84 records, yet when I do a manual filter I get 80 records.  The correct number is 80.   Can anyone see anthing wrong with the measure below.     T...
  • goncalogeraldes's avatar
    goncalogeraldes
    4 years ago

    Hello there spandy34 ! Hope this works for you!

    ztest_StopLoss__Open_EL_PL =
COUNTROWS (
    CALCULATETABLE (
        'Main Claim Data Distinct',
        'Main Claim Data Distinct'[DBA_VW5_STATUS_FLAG_LOOKUP.Description] = "Open",
        'Main Claim Data Distinct'[ClassOfBusinessCode]
            IN { "EL", "PL" }
            && NOT ( 'Main Claim Data Distinct'[PolicyCode] )
            IN { "CO", "LPP", "LPL", "AS", "HD", "LEL", "ML", "PI", "TEL", "TP" }
            && 'Main Claim Data Distinct'[SourceCode] <> "INS"
    )
)

     Hope this answer solves your problem!
    If you need any additional help please @ me in your reply.
    If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
    Thanks!

    You can also check out my LinkedIn!

    Best regards,
    Gonçalo Geraldes

spandy34's avatar
spandy34
Icon for Responsive Resident rankResponsive Resident
4 years ago

The issue is that the calculation is not filtering out those records that contain the letters CO", "LPP", "LPL", "AS", "HD", "LEL", "ML", "PI", "TEL", "TP".  These are not exact contents they are contains is ML30LPL, M26TEL, TP52OAJ

 

Just wondered if anyone could review my measure and see where Im going wrong

 

z_StopLoss__Open_EL_PL =
COUNTROWS (
CALCULATETABLE (
'Main Claim Data Distinct',
'Main Claim Data Distinct'[DBA_VW5_STATUS_FLAG_LOOKUP.Description] = "Open",
'Main Claim Data Distinct'[ClassOfBusinessCode]
IN { "EL", "PL" }
&& NOT ( 'Main Claim Data Distinct'[PolicyCode]
IN { "CO", "LPP", "LPL", "AS", "HD", "LEL", "ML", "PI", "TEL", "TP" } )
&& 'Main Claim Data Distinct'[SourceCode] <> "INS"
)
)
goncalogeraldes's avatar
goncalogeraldes
Icon for Super User rankSuper User
4 years ago

Hello there spandy34 ! Hope this works for you!

ztest_StopLoss__Open_EL_PL =
COUNTROWS (
    CALCULATETABLE (
        'Main Claim Data Distinct',
        'Main Claim Data Distinct'[DBA_VW5_STATUS_FLAG_LOOKUP.Description] = "Open",
        'Main Claim Data Distinct'[ClassOfBusinessCode]
            IN { "EL", "PL" }
            && NOT ( 'Main Claim Data Distinct'[PolicyCode] )
            IN { "CO", "LPP", "LPL", "AS", "HD", "LEL", "ML", "PI", "TEL", "TP" }
            && 'Main Claim Data Distinct'[SourceCode] <> "INS"
    )
)

 Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes