Forum Discussion
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
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 !!
10 Replies
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
Yes the green table is from Power Bi
Name for the VIsual Claim Nos ReceivedNo of Claims = COUNT('Main Claim Data'[ClaimRef])Name for the VIsual Repudiation RateNo of Claims = ([No Repudiated]/[No of Claims])*100Name for the VIsual Repudiated Claim NosNo 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] )
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?