Measures- Multiple Fields Criteria

[spandy34]

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

[tamerj1 · Accepted 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".

[spandy34]

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

[tamerj1 · Accepted 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".

[spandy34]

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?

[tamerj1]

That is because no of you records matches its conditions 

[spandy34 · Accepted solution]

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

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

[spandy34]

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

[tamerj1]

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

[spandy34]

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]

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

[tamerj1]

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