Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
15 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |