Finding the error

[DemoFour]

Afternoon all,

I have built a report and have a nice star schema with 2 fact tables, I have a series of measures to show me different scores, but in one set of measures I get the full data output, but in another one, one person is missing from the output! 

I have removed the names but the data is the same - different views as I am checking results - don't judge my visualization skills its not the end product 🙂 

As you can see the Dynamic score is 10 less, the exact score of the missing person in the first matrix bottom right! This person is missing in both Viewing and Building outputs.

My question is how can I debug this, and why is the one measure correctly picking up all staff but the other measure is not? 

I have checked my tables and data but nothing is wrong here? 

DAX below for reference, I cant post a file as I have no where to store it and post up. 

First Viewing Score = 
VAR FirstScore = MIN('Skills Audit'[Survey Index] )
VAR Result =
CALCULATE(
    [Total Audit Score],
    FILTER(
        'Skills Audit',
        'Skills Audit'[Survey ID] = 'Skills Audit'[Survey ID]
    ),
    'Skills Audit'[Survey Index] = FirstScore,
    Question[Category] = "Viewing"
)
RETURN
    Result 

Total audit score is a SUM measure of the scores

This code is taken from DAX Pattens - Dynamic segmentation – DAX Patterns

Viewing Score Dynamic = 
   SUMX(
       VALUES('Date'[Month Name] ),           -- Repeat segmentation for every month selected  
    VAR SkillsMatrixStaff =                     -- Gets the customers in the current segment
        FILTER(
            ALLSELECTED( 'Staff Member' ),
            VAR SkillsMatrixScore = [Viewing Score]        -- Computes the Skill Matrix Score for one staff memeber 
            VAR CategoryForSurvey =                         -- Retrieves the segment
                FILTER(                                     -- a customer belongs to
                    'Skills Ratings Segments',
                    NOT ISBLANK( SkillsMatrixScore )
                        && 'Skills Ratings Segments'[Min Viewing Score] < SkillsMatrixScore
                        && 'Skills Ratings Segments'[Max Viewing Score] >= SkillsMatrixScore
                )
            VAR IsStaffInCategory =
                NOT ISEMPTY( CategoryForSurvey )
            RETURN
                IsStaffInCategory
        )
    VAR Result =
        CALCULATE(
            [Viewing Score],               -- Expression to Compute - this is the total score per staff member each time
            KEEPFILTERS( SkillsMatrixStaff )  -- Applies filter for segmented customers 
        )
    RETURN
        Result
)

ANy help and advice on how I can find the missing value would be greatly received. 

[Anonymous]

Hi @DemoFour , 

Your calculation is based on your data model. Please share a sample file with us and show us a screenshot with the result you want. I need to know the calculate logic of the measures you used in dynamic score. This will make it easier for me to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.