Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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
Solved! Go to Solution.
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".
Hi - It was my fault, it was Highway Maintenance not Highways.
THis is sorted and I really appreciate all your help - Unbelievable !!
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".
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?
That is because no of you records matches its conditions
Hi - It was my fault, it was Highway Maintenance not Highways.
THis is sorted and I really appreciate all your help - Unbelievable !!
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
Hi @spandy34
Can you share the code of your measures? Is the green table from power bi?
Yes the green table is from Power Bi
@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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
15 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
12 |