Forum Discussion

spandy34's avatar
spandy34
Icon for Responsive Resident rankResponsive Resident
4 years ago
Solved

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...
  • tamerj1's avatar
    tamerj1
    4 years ago
    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's avatar
    spandy34
    4 years ago

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

     

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

spandy34's avatar
spandy34
Icon for Responsive Resident rankResponsive Resident
4 years ago

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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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

  • spandy34's avatar
    spandy34
    Icon for Responsive Resident rankResponsive Resident
    4 years ago

    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] )
    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      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

    • spandy34's avatar
      spandy34
      Icon for Responsive Resident rankResponsive Resident
      4 years ago

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