Calculating the percentages of value appearence from multiple rows against a different table

[Flawn]

Hello all - this is my second time writing this up because the forums ate it the first time! I thank you all in advance for any help you are able to provide as it's something of a whopper of a question - with multiple parts. I'll do my best to keep it clear and succinct. I've provided a link to a simplified, anonymized, and reduced sample of the data in question in an excel format- I can't provide the full pbix due to concerns about the highly confidential nature of our database. Each page of the excel sheet represents a different table in the database.

https://docs.google.com/spreadsheets/d/1m9x73zpdcSO-_zFb5UELXZemzR6DOB5s/edit?usp=sharing&ouid=10431...

The basic structure of the data is as such. Every Client in Case(CiC) has at least one associated row (usually multiple) in the Client in Session(CiS) table - with a one to many relationship. *Many but not all* CiS rows have an associated row in the SCORE table with a one-to-man relationship. I've tried to merge these tables together but it's proven to be quite messy, with a great deal of duplication that lead to fairly dirty reports. I'd prefer any solution to the problems i'm facing to utilize relationships rather than merging, but i'm willing to bite the bullet and merge tables if that proves neccessary.

The most important of these tables for our purposes is the "SCORE" Table, which has the ID Code for its parent CiS entry in the CiS table, the "pre/post" column, and 6 'assessment' columns (in truth there are 20, but I simplified it for the sample data). All of the assessment columns can be broken up into three 'assessment categories': Circumstances, Goals, and Satisfaction. For the purposes of this question, we dont need to worry about the individual columns so much as the categories themselves.

So what do we actually need from this data?:

1. #/% of CiS codes with at least one associated SCORE row with at least *one* non-blank value in any of the assessment columns.
2. #/% of CiS codes with at least TWO associated Score rows - at least one of them having a "pre" value in the pre/post column and at least one of them having a "post" value in the pre/post column. Rows are only counted for this calculation if at least *one* entry in any of the assessment columns is non-blank.
3. Same as 1 but broken up by Assessment Category. I have attached an image as an example - marking with blue each of the rows that would need to be counted when calculating the #/% of CiS entries for the "Circumstance" assessment category. So in this case we wouldn't consider any values entered into the "Goals" or "satisfaction" categories - we only count rows with at least one non-blank value in the "Circumstances" columns.

4. Same as 2, but with the same stipulations as 3. Once again I have attached an example to indicate which rows would need to be counted when calculating the #/% of CiS entries for the "Circumstance" assessment category. As we can see in this example, only rows that have a Pre and Post entry for the *same* CiS ID number are included in the count - and once again we must break the count up by category - one for Circumstances, one for Goals, and one for Satisfaction.

But we're not done yet! Then we must perform the same 4 calculations against the CiC codes! So we'd have to move up the relationship ladder to counting the SCORE rows against the CiC codes, which can only be done through the intermediary step of the CiS codes... if that makes any sense?

I know this is a massive ask, and will almost certainly involve a good many steps/measures to complete. But all I really need is some guidance on how to construct the DAX formulaes/measurements for each of the 4 questions... as well as how to perform the same claculations when counting against the CiC codes instead of against the CiS Codes.

I'll try my best to clarify where needed; don't hesitate to ask! I thank you again, in advance, for any help you can provide.

[v-kaiyue-msft]

Hi @Flawn ,

I don't really understand your needs, could you simplify your data by keeping only some representative data and then create a virtual table to manually write the results you want to get and present it in a screenshot or other easy to understand way.

This way I will be able to understand you better and help you get the desired results.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[Flawn]

Hey thanks for the help Clara,
As requested, here's a screenshot of a table with mockup data that simplifies the initial dataset alongside a table of the expected results for each of the objectives.
 

The desired result columns are based on the distinct number of CiS Ids that meet the conditions of each objective. Objective 3 and 4 are broken up into the two different assessment categories to get a better view of how they should be counted.

Thanks again for the help, and I'm more than happy to provide any further clarification/visuals that may be needed!