topic Re: Measures- Multiple Fields Criteria in DAX Commands and Tips

Measures- Multiple Fields Criteria

spandy34 — Fri, 18 Feb 2022 15:40:30 GMT

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

Re: Measures- Multiple Fields Criteria

spandy34 — Fri, 18 Feb 2022 17:07:59 GMT

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

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

Re: Measures- Multiple Fields Criteria

tamerj1 — Sat, 19 Feb 2022 10:09:40 GMT

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

Re: Measures- Multiple Fields Criteria

spandy34 — Sat, 19 Feb 2022 10:29:10 GMT

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] )

Re: Measures- Multiple Fields Criteria

tamerj1 — Sat, 19 Feb 2022 10:47:32 GMT

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

Re: Measures- Multiple Fields Criteria

spandy34 — Sat, 19 Feb 2022 10:57:22 GMT

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

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

Re: Measures- Multiple Fields Criteria

tamerj1 — Sat, 19 Feb 2022 11:12:55 GMT

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".

Re: Measures- Multiple Fields Criteria

spandy34 — Sat, 19 Feb 2022 11:39:04 GMT

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?

Re: Measures- Multiple Fields Criteria

spandy34 — Sat, 19 Feb 2022 11:47:26 GMT

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

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

Re: Measures- Multiple Fields Criteria

tamerj1 — Sat, 19 Feb 2022 11:48:06 GMT

That is because no of you records matches its conditions

Re: Measures- Multiple Fields Criteria

spandy34 — Mon, 21 Feb 2022 10:05:34 GMT

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