Forum Discussion

Richard_Halsall's avatar
Richard_Halsall
Helper IV
2 years ago
Solved

Show Matrix Total as count when values are text

Hi 

After some help please

 

I have a fact table linked to a date table for which I have created the matrix below

 

The value field is text with summarization of 'First'

 

I would like the total field to be a count where the technician name is not No Technician i.e. for both 22/05/2024 & 23/05/2024 this would be 2

 

The sample data for the fact table would be, the date table is a standard date table

AllocatedDateTeamRoleTechnicianName
22/05/2024Team 1Blade Tech 2:1Joel
22/05/2024Team 1Blade Tech 2:2Michael
22/05/2024Team 1Blade Tech 2:5No Technician
22/05/2024Team 1Blade Tech 3:1No Technician
23/05/2024Team 1Blade Tech 2:1Joel
23/05/2024Team 1Blade Tech 2:2Michael
23/05/2024Team 1Blade Tech 2:5No Technician
23/05/2024Team 1Blade Tech 3:1No Technician

 

Is this possible

 

Thanks in advance

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Richard_Halsall 

     

    You can create this measure and add it to the matrix to replace the original value field of First summarization. 

    ValueMeasure = IF(ISINSCOPE('Table'[Role]),MAX('Table'[TechnicianName]),COUNTROWS(FILTER('Table','Table'[TechnicianName]<>"No Technician")))

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Richard_Halsall 

     

    You can create this measure and add it to the matrix to replace the original value field of First summarization. 

    ValueMeasure = IF(ISINSCOPE('Table'[Role]),MAX('Table'[TechnicianName]),COUNTROWS(FILTER('Table','Table'[TechnicianName]<>"No Technician")))

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!