Grouping in Power BI

[Chitemerere]

I have a called QualityFacts:

I have the following measure:

which gives the following results put in a card:

I need to group the values in columns "Assay API 1", "Assay API 2" and "Assay API 3".  Assay API 1 does not have any blank values as this is a mandatory field value but Assay API 2 and Assay API 3 can be blank depending on the product/record.  I have created the following grouping table called "Assay Group" which has no relationship to the QualityFacts table, i.e. a supporting or secondary table:

The sort order column is used for sorting the "Assay Group" column which is text.  What i want to do is group the "Assay API 1", "Assay API 2" and "Assay API 3" from the "QualityFacts" table based on the "Assay Group" table above.  I am using the following measure for "Assay API 1":

However the results i am getting are not what i am expecting for "Assay API 1" where i am getting the following:

 As shown in the card above, the total number of samples is 126 so i expect the grouping above to give this total.  Please assist with the right measure.

How would i extend the recommended measure to cater for "Assay API 2" and "Assay API 3" which may contain blank values for certain products/records.

Best regards,

Chris

[amitchandak · Accepted solution]

@Chitemerere , refer if this can help

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[Chitemerere]

Many thanks, the first two references are great, i had to buy the book in which details are discussed and managed to solve my problem.

Best regards,

Chris

[Chitemerere]

Further to my last response, if I put the value of the measure in a card i get the following:

 The above is in contrast to the values which appear in the grouped table below:

so there is something i am getting wrong in the table.

Regards,

Chris

[Chitemerere]

Thank you very much for your prompt response, mos appreciated.

My approach is similar to the first example you referred me to on "Dynamic segmentation".  The other examples are not suitable for my situation.  However the "Dynamic segmentation" example gives the same results as my approach.

Best regards,

Chris