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

 

 

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

      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?

       

       

      • spandy34's avatar
        spandy34
        Icon for Responsive Resident rankResponsive Resident
        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 !!