Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
spandy34
Responsive Resident
Responsive Resident

DAX FILTER

‎04-07-2022 06:49 AM

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    
Message 1 of 4
490 Views
0
1 ACCEPTED SOLUTION
goncalogeraldes
Super User
Super User
In response to spandy34

‎04-08-2022 05:26 AM

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

View solution in original post

Message 3 of 4
430 Views
3 REPLIES 3
spandy34
Responsive Resident
Responsive Resident

‎04-07-2022 08:58 PM

@tamerj1 

Message 4 of 4
441 Views
0
spandy34
Responsive Resident
Responsive Resident

‎04-07-2022 08:59 AM

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"
)
)
Message 2 of 4
452 Views
0
goncalogeraldes
Super User
Super User
In response to spandy34

‎04-08-2022 05:26 AM

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

Message 3 of 4
431 Views

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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

View All