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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
spandy34
Responsive Resident
Responsive Resident

Measures- Multiple Fields Criteria

Hi

 

I wondered if anyone can help me - I am new to DAX - I am trying to create a measure with multiple critieria - I want to count the number of records - Table Name is Main Claim Data - the field i want to count is ClaimRef, where the field ClassOfBusiness = PL and the Department = Highways and the StatusFlag = CLOSED

 

I know how to count with one criteria but unsure with multiple - please can someone help

 

 

 

 

2 ACCEPTED SOLUTIONS

Category =
SWITCH (
    TRUE,
    'Main Claim Data'[ClassOfBusiness] = "PL"
        && 'Main Claim Data'[Department] = "Highways"
        && 'Main Claim Data'[StatusFlag] = "CLOSED", "Highways PL",
    'Main Claim Data'[ClassOfBusiness] = "PL"
        && 'Main Claim Data'[Department] <> "Highways"
        && 'Main Claim Data'[StatusFlag] = "CLOSED", "Other PL",
    'Main Claim Data'[ClassOfBusiness] = "EL"
        && 'Main Claim Data'[StatusFlag] = "CLOSED", "EL",
    "Others"
)


A category "Others" will polute your report. The easiest way to get red of it is to create a slicer of the category column and select the options you need and deselect "Others".

View solution in original post

spandy34
Responsive Resident
Responsive Resident

Hi - It was my fault, it was Highway Maintenance not Highways.

 

THis is sorted and I really appreciate all your help - Unbelievable !! 

View solution in original post

10 REPLIES 10
spandy34
Responsive Resident
Responsive Resident

 

 

Brilliant - are you able to tell me the coding that would create each column as I havent done a calculated column and dragged it into a a table before - based on information below - I really do appreciate your help with this.

 

 

Measure Highways PL

Table Name is Main Claim Data - I need them to filter the records where the field ClassOfBusiness = PL and the Department = Highways and the StatusFlag = CLOSED

 

Other PL

Table Name is Main Claim Data - I need them to filter the records where the field ClassOfBusiness = PL and the Department IS NOT Highways and the StatusFlag = CLOSED

 

 

 

EL

Table Name is Main Claim Data - I need them to filter the records where the field ClassOfBusiness = EL and and the StatusFlag = CLOSED

Category =
SWITCH (
    TRUE,
    'Main Claim Data'[ClassOfBusiness] = "PL"
        && 'Main Claim Data'[Department] = "Highways"
        && 'Main Claim Data'[StatusFlag] = "CLOSED", "Highways PL",
    'Main Claim Data'[ClassOfBusiness] = "PL"
        && 'Main Claim Data'[Department] <> "Highways"
        && 'Main Claim Data'[StatusFlag] = "CLOSED", "Other PL",
    'Main Claim Data'[ClassOfBusiness] = "EL"
        && 'Main Claim Data'[StatusFlag] = "CLOSED", "EL",
    "Others"
)


A category "Others" will polute your report. The easiest way to get red of it is to create a slicer of the category column and select the options you need and deselect "Others".

spandy34
Responsive Resident
Responsive Resident

Hi 

 

We are nearly there but I have no Highways PL column only Other PL and EL as on diagram.  I have put the slider in as suggested.  How do I get Highways PL please?

 

spandy34_0-1645270725224.png

 

That is because no of you records matches its conditions 

spandy34
Responsive Resident
Responsive Resident

Hi - It was my fault, it was Highway Maintenance not Highways.

 

THis is sorted and I really appreciate all your help - Unbelievable !! 

spandy34
Responsive Resident
Responsive Resident

Just reviewing this .  I dont want to count the records as they are already being counted by the Mesaures in the row   - if you see on the attached (green table) I have set up a matrix but need the matrix to have the headers Highways PL Other PL EL

 

What I think I need are measure that filter the colum records as follows-

 

Measure Highways PL

Table Name is Main Claim Data - I need them to filter the records where the field ClassOfBusiness = PL and the Department = Highways and the StatusFlag = CLOSED

 

Other PL

Table Name is Main Claim Data - I need them to filter the records where the field ClassOfBusiness = PL and the Department IS NOT Highways and the StatusFlag = CLOSED

 

 

EL

Table Name is Main Claim Data - I need them to filter the records where the field ClassOfBusiness = EL and and the StatusFlag = CLOSED

 

 

spandy34_0-1645203736501.png

spandy34_1-1645203760902.png

 

 

 

Hi @spandy34 
Can you share the code of your measures? Is the green table from power bi?

spandy34
Responsive Resident
Responsive Resident

Yes the green table is from Power Bi

 

Name for the VIsual Claim Nos Received
No of Claims = COUNT('Main Claim Data'[ClaimRef])
 
Name for the VIsual Repudiation Rate
No of Claims = ([No Repudiated]/[No of Claims])*100
 
Name for the VIsual Repudiated Claim Nos 
No Repudiated = [Count of RepudiatedFlag for True]
Repudiated £ = SUMX ( FILTER ( 'Main Claim Data', [RepudiatedFlag] = TRUE () ), [Highest Estimate] )
 
Name for the Visual Repudiated £ 
Repudiated £ = SUMX ( FILTER ( 'Main Claim Data', [RepudiatedFlag] = TRUE () ), [Highest Estimate] )
spandy34
Responsive Resident
Responsive Resident

@goncalogeraldes I have had this resolved now but thank you for responding.  Much appreciated

Ok Then you need to create a calulated column that delivers the 3 categories (Highways PL, Other PL & EL) based on your condition with simple IF statements.
Then drag the new column into your matrix's culmns

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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