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.  

 

The criteria is the number of records where status is Open, Class of Business = EL or PL and the Policy Code is NOT "CO", "LPP", "LPL", "AS", "HD", "LEL", "ML", "PI", "TEL", "TP" and the Source Code is NOT INS

 

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"
)
)
 
 
Here is the Data Table where I get 80 records
 
PolicyYearSourceCodeClaimRefTotalClaimPolicyCodeCount of ClaimRefSum of OutstandingEstimateNetDBA_VW5_STATUS_FLAG_LOOKUP.DescriptionClassOfBusinessCode
Year 2021 M21PL0000531M21PL11 OpenPL
Year 2021 M21PL0000931M21PL11 OpenPL
Year 2021 M21PL00008337.5M21PL137.5 OpenPL
Year 2021LAM21PL00008056M21PL1056OpenPL
Year 2021 M21PL00004365M21PL165 OpenPL
Year 2021 M21PL00007480M21PL180 OpenPL
Year 2021 M21PL00010883M21PL183 OpenPL
Year 2021 M21PL00006496M21PL196 OpenPL
Year 2021 M21PL00008799.99M21PL199.99 OpenPL
Year 2021 M21PL000109120M21PL1120 OpenPL
Year 2021 M21PL000077140M21PL1140 OpenPL
Year 2021 M21PL000094145M21PL1145 OpenPL
Year 2021 M21PL000015150M21PL1150 OpenPL
Year 2021 M21PL000097165M21PL1165 OpenPL
Year 2021 M21PL000088166M21PL1166 OpenPL
Year 2021 M21PL000103180M21PL1180 OpenPL
Year 2021 M21PL000060223.75M21PL1223.75 OpenPL
Year 2021 M21PL000035235.13M21PL1235.13 OpenPL
Year 2021 M21PL000071250M21PL1250 OpenPL
Year 2021 M21PL000086258.5M21PL1258.5 OpenPL
Year 2021 M21PL000079265M21PL1265 OpenPL
Year 2021 M21PL000110340M21PL1340 OpenPL
Year 2021 M21PL000104358M21PL1358 OpenPL
Year 2021 M21PL000017392M21PL1392 OpenPL
Year 2021 M21PL000106400M21PL1400 OpenPL
Year 2021 M21PL000100450M21PL1450 OpenPL
Year 2021 M21PL000006500M21PL1500 OpenPL
Year 2021 M21PL000040500M21PL1500 OpenPL
Year 2021 M21PL000063500M21PL1500 OpenPL
Year 2021 M21PL000085500M21PL1500 OpenPL
Year 2021 M21PL000105650M21PL1650 OpenPL
Year 2021 M21PL000090830M21PL1830 OpenPL
Year 2021 M21PL000057850M21PL1850 OpenPL
Year 2021 M21PL000092950M21PL1950 OpenPL
Year 2021 M21PL000091983M21PL1983 OpenPL
Year 2021LAM21PL000072995M21PL10995OpenPL
Year 2021 M21PL0000621000M21PL11000 OpenPL
Year 2021 M21PL0000651000M21PL11000 OpenPL
Year 2021 M21PL0000411315.33M21PL11315.33 OpenPL
Year 2021 M21PL0000241350.4M21PL11350.4 OpenPL
Year 2021 M21PL0000161500M21PL11500 OpenPL
Year 2021 M21PL0000542000M21PL12000 OpenPL
Year 2021 M21PL0000462500M21PL12500 OpenPL
Year 2021 M21PL0000262800M21PL12800 OpenPL
Year 2021 M21PL0000442800M21PL12800 OpenPL
Year 2021 M21PL0000523000M21PL13000 OpenPL
Year 2021 M21PL0000753544M21PL13544 OpenPL
Year 2021 M21PL0000953544M21PL13544 OpenPL
Year 2021 M21PL0000273824M21PL13824 OpenPL
Year 2021 M21PL0000663900M21PL13900 OpenPL
Year 2021 M21PL0000514000M21PL14000 OpenPL
Year 2021 M21PL0000474394M21PL14394 OpenPL
Year 2021 M21PL0000304824M21PL14824 OpenPL
Year 2021 M21PL0000385000M21PL15000 OpenPL
Year 2021LAM21PL0000455000M21PL14962.5637.44OpenPL
Year 2021 M21PL0001075244M21PL15244 OpenPL
Year 2021 M21EL0000025809M21EL15809 OpenEL
Year 2021 M21PL0001016074M21PL16074 OpenPL
Year 2021 M21PL0000336744M21PL16744 OpenPL
Year 2021 M21PL0000786759M21PL16759 OpenPL
Year 2021 M21PL0000507084M21PL17084 OpenPL
Year 2021 M21PL0000557440M21PL17440 OpenPL
Year 2021 M21PL0000967500M21PL17500 OpenPL
Year 2021 M21PL0001027904M21PL17904 OpenPL
Year 2021 M21PL0000688504M21PL18504 OpenPL
Year 2021 M21PL0000769077M21PL19077 OpenPL
Year 2021 M21PL00004810000M21PL110000 OpenPL
Year 2021 M21PL00004910000M21PL110000 OpenPL
Year 2021 M21PL00007310000M21PL110000 OpenPL
Year 2021 M21PL00008210000M21PL110000 OpenPL
Year 2021 M21PL00011110000M21PL110000 OpenPL
Year 2021 M21PL00008910244M21PL110244 OpenPL
Year 2021 M21PL00009810244M21PL110244 OpenPL
Year 2021 M21PL00006910744M21PL110744 OpenPL
Year 2021 M21PL00003211160M21PL111160 OpenPL
Year 2021LAM21EL00000113550M21EL174410236OpenEL
Year 2021 M21PL00011214044M21PL114044 OpenPL
Year 2021 M21PL00004214080M21PL114080 OpenPL
Year 2021 M21PL00005914880M21PL114880 OpenPL
Year 2021 M21PL00007015794M21PL115794 OpenPL
     80    
  • 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

3 Replies

  • 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