Filtering across multiple tables and relationships

[BM4291]

Hi,

Struggling to make some sense of a few measures and hoping someone might be able to see the wood for the trees on this!

Problem

I have three fact tables and a dimension table, I need to use information from each of these tables to understand activity on my data; the schema is below but I have redacted the tables:

I currently have measures set up to give me the number of 'Hearings' and 'HearingCases':

MEASURE Hearings[hearings] = DISTINCTCOUNT(Hearings[HearingID])
MEASURE Hearings[hearingsCases] = DISTINCTCOUNT(Hearings[CaseID])

I now need to extend these to look at the hearings on closed cases (this is identified by the relationship between 'Case'[ClosedCase] and Dates[Date]) as below:

MEASURE Hearings[hearingsClosedCase] = CALCULATE([hearings], USERELATIONSHIP(Dates[Date], 'Case'[CaseClosed]))
MEASURE Hearings[hearingsCasesClosed] = CALCULATE([hearingsCases], USERELATIONSHIP(Dates[Date], 'Case'[CaseClosed]))

I now need to take this one step further and identify cases with a specific 'Application' type; cases can have multiple applications; in a standalone DAX query I'm able to get the correct answer which I can validate against the raw data in SQL:

//QA Dip-Sample for Oct-22 data; X can be any integer; returns correct result
EVALUATE
CALCULATETABLE(
	Hearings
	, FILTER('Case'
		, [CaseClosed] >= DATE(2022, 10, 01)
		&& [CaseClosed] <= DATE(2022, 10, 31)
	)
	, FILTER(Applications
		, [AppType] = X
	)
)

//QA Dip-Sample with wrong outcome
EVALUATE
CALCULATETABLE(
	Hearings
	, FILTER(Applications
		, [AppType] = X
	)
	, USERELATIONSHIP(Dates[Date], 'Case'[CaseClosed])
)

When I try to turn this in a measure, I cannot get the right answer; it either matches [hearings] or returns blank; the other method I've used to QA returns 6 rows v. 359 (as validated above).

 An example of some measures I've tried and return the wrong values are below; I'm certain the issue is on the context of the filter, but I just can't re-create the logic of my working table above.

//X being a hard-coded integer
MEASURE Hearings[hearingsClosedCaseX] = CALCULATE([hearingsClosedCase], Applications[AppType] = X) //This results in the same value as [hearingsClosedCase] which is not correct

MEASURE Hearings[hearingsClosedCaseX] = CALCULATE([hearingsClosedCase], FILTER(Applications, [AppType] = X)) //This returns blank for most months which I know isn't true

MEASURE Hearings[hearingsClosedCaseX] = CALCULATE(CALCULATE([hearingsClosedCase], FILTER(Applications, [AppType] = 126)), USERELATIONSHIP(Dates[Date], 'Case'[CaseClosed])) //Takes an age to run > 5 mins in DAX studio locally and returns ~4/5 rows less than the correct DAX query above... The closest I can get!

Ultimately, I need to show the data at a monthly level but may need to rollup to yearly etc depending on requirements.

[BM4291 · Accepted solution]

@lbendlin @v-yiruan-msft Apologies for the delay, it has been a busy few days and this went on the backburner; I had to use the SQL version to get the data across but have re-visited and got the answer... I don't think this is necessarily the most performant, and there may be a better way to reach the conclusion but see below:

MEASURE Hearings[hearingsClosedCaseX] =
//Hearings that appear on Cases Closed with X with Hearings
COUNTROWS(
	DISTINCT(
		NATURALINNERJOIN(
			CALCULATETABLE(
			TREATAS(
				SELECTCOLUMNS(
					FILTER(Applications
							, [AppType] = X
					)
				, "CaseID", [CaseID]
				)
				, Hearings[CaseID]
			)
			, USERELATIONSHIP(Dates[Date], 'Case'[CaseClosed])
			)
		, CALCULATETABLE(Hearings, USERELATIONSHIP(Dates[Date], 'Case'[CaseClosed]))
		)
	)
)

MEASURE Hearings[hearingsCasesClosedX] = 
//Cases Closed with a X with Hearings
COUNTROWS(
	DISTINCT(
		CALCULATETABLE(
			TREATAS(
				SELECTCOLUMNS(
					FILTER(Applications
							, [AppType] = X
					)
				, "CaseID", [CaseID]
				)
				, Hearings[CaseID]
			)
			, USERELATIONSHIP(Dates[Date], 'Case'[CaseClosed])
		)
	)
)

[v-yiruan-msft]

Hi @BM4291 ,

In order to get a better understanding on your requirement and give you a suitable solution, could you please provide some raw data in the table 'Hearings','Applications','Dates' and 'Case' and some special examples to explain the backend logic and correct result? It may be not necessary to create any relationships among the tables base on multiple fields... Thank you.

Best Regards

[lbendlin]

Your data model can benefit from a cleanup.  Generally you do not want to link fact tables together. Instead, have them controlled by the dimensions they share.  Once your data model is cleaned the measures will work as expected.

I assume you are familiar with the concepts of star schema and snowflake schema?

[BM4291]

Thanks @lbendlin,

In general, the model is using a snowflake schema to the point where it proved difficult; there are certainly areas it could be tidied up which I'm certainly open to however am struggling when it comes to these (and a handful of other) tables which don't really share a dimension unless we started to introduce some additional dates which may increase data redundancy.

In a nutshell, the core 'fact' table is 'Case' and can have multiple 'Hearing' and 'Application' events attached which are essentially dimensions joined by the CaseID.

Each of those events then have their own variety of dates attached, as an example, one set of measures may need to use the closure date from the 'Case' table or the date of the hearing from 'Hearings', are you suggesting move the case closure into hearings and break the relationship on CaseID?

I'm not sure how this necessarily helps with the initial question of where I'm trying to control the filter context from the different dimensions but open to any thoughts/suggestions!