Help only taking into account of data that meets certain criteria

[xpisoverated]

Hi, i was hoping people could help, i have the following situation.

I have 10 rows of data per item (lets call it a service name for now), each row of data is the answer to a compliance question, but as these are asked independantly of each other they are separate rows of data, rather than fields in a single row of data.

For example

Service A will have 10 questions, lets say Q1-Q10, it will always be 10 rows of data.

Inside that row of data one of the fields will indicate that the question has been correctly answered, i.e. the question has been reviewed, for that field (lets call it review_status, the options will be complete, incomplete). Another field in the question will indicate the actual answer to the question, of yes or no.

I have 500 "Services", so 5000 rows of data, with one of the fields containing the service name.

I want to see which of the 10 questions is the most commonly answerd "No" but only where all 10 questions for that service have a review_status of complete. I.e. i only want to see the most common No answer once a review of that service is complete.

Any ideas on how i would do this, my ultimate goal is a list table showing the most common 5 questions with an answer of No for only services where the review is fully complete (all 10 questions).

(In reality i have more than 10 questions, but wanted to keep the logic simple for the example)

[v-abhinavmu]

Hi @xpisoverated,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

Thank you.

[danextian]

Hi @xpisoverated 

Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX (confidential data removed) stored in the cloud.

[ryan_mayu]

@xpisoverated 

could you pls provide some sample data and expected output?

[Shai_Karmani]

Add a calculated column on your data table that flags fully-reviewed services, then count Nos against that flag. The flag works for any number of questions per service because it just checks whether the service has any incomplete row.

Calculated column:

Service Is Fully Reviewed =
VAR HasIncomplete =
    CALCULATE (
        COUNTROWS ( 'Data' ),
        'Data'[review_status] = "incomplete",
        ALLEXCEPT ( 'Data', 'Data'[Service] )
    )
RETURN HasIncomplete = 0

Measure:

No Count Fully Reviewed =
CALCULATE (
    COUNTROWS ( 'Data' ),
    'Data'[Answer] = "No",
    'Data'[Service Is Fully Reviewed] = TRUE
)

Put Question on a table visual, this measure as the value, sort descending and apply a Top N filter of 5. Replace 'Data' with your actual table name.

If this helped, a thumbs up and accepting the solution would be appreciated.

Thank you,
Shai Karmani

[xpisoverated]

Thanks i had to tweak it a little to get the result i wanted, so just wanted to double check why it worked without the allexcept line.

My final code looked like this (i reversed the checked condition to check for not equal to complete rather than incomplete to handle any value other than complete, e.g. draft, blank, etc)

Service Is Fully Reviewed =
VAR HasIncomplete =
    CALCULATE (
        COUNTROWS ( 'Data' ),
        'Data'[review_status] <> "complete"
    )
RETURN HasIncomplete = 0

This worked giving me a true/false column i could filter on in the measure, but didnt work if i left the allexcept line in (but i dont really understand all except).

[v-abhinavmu]

Hi @xpisoverated,
Thanks for reaching out to the Microsoft Fabric Community forum.

ALLEXCEPT ( 'Data', 'Data'[Service] ) tells DAX to keep only the Service filter and ignore the others. This allows the calculation to check all rows/questions for the same Service instead of only the current row, which helps correctly identify whether the entire Service is fully reviewed.

So your logic is valid, but using ALLEXCEPT is generally more robust and safer if additional filters or relationships are added later.

For more details, please refer to the official documentation:
ALLEXCEPT function (DAX) - DAX | Microsoft Learn

I hope this helps. Please feel free to reach out if you have any further questions.
Thank you.