Filter data in one table based on another unrelated table

[RachnaVerma]

Hi Guys

In the tabular model we have two date related tables - Date1 and Date2.

Date1 table is related to the Fact table - Inpatient based on PatientDischargeDate.

Date2 table is related to the Fact table - Outpatient based on AppointmentStartDate.

The reason for having two date dimension tables is that there are a couple of Fact tables that store key column values from both Inpatient and Outpatient depending upon whether that record corresponds to Inpatient or Outpatient. And these Fact tables have bi-directional relationship with both Inpatient and Outpatient Fact tables.

Having a single date table related to both Inpatient and Outpatient was not allowing to create bidirectional relationship for these tables due to ambiguous paths.

Hence had to create two separate Date dimensions as stated above.

I need to calculate in a DAX measure the total charge which will be sum of Outpatient Visit Charge and Inpatient Stay Charge.

The date filter will be coming from Date1 table(that is not related to Outpatient table), still the measure should be able to calculate Outpatient Visit Charge correctly for a month/year.

I tried to calculate the Outpatient Visit Charge through below code :

DEFINE MEASURE 'Outpatient'[Outpatient Visit Charge] =

VAR mStartDate = MIN('Date1[Date])

VAR mEndDate = MAX('Date1'[Date])

VAR STest = CALCULATETABLE( VALUES('Date2'[DATE]), FILTER ('Date2','Date2'[Date] = mStartDate ))

VAR ETest = CALCULATETABLE( VALUES('Date2'[DATE]), FILTER ('Date2','Date2'[Date] = mEndDate ))

VAR OutpatientVisitCharge = CALCULATE( SUM ('Outpatient'[VisitCharge]),

    'Outpatient'[IsFundable] = TRUE (),

    DATESBETWEEN('Date2'[Date], STest, ETest ))

RETURN  OutpatientVisitCharge 

EVALUATE ROW ("Outpatient Visit Charge",CALCULATE( 'Outpatient'[Outpatient Visit Charge],

   'Date1'[Financial Year Short Name] = "2023-24",

   'Date1'[Financial Month Name] = "March")

           )

But this is not working it returns a blank value. On returning the STest and ETest variables values I can see it is returning correct dates as in 01-Mar-2024 and 31-Mar-2024 but can't figure out why it is returning blank for OutpatientVisitCharge.

In the above code if I make below changes - 

VAR STest = CALCULATETABLE( VALUES('Outpatient'[AppointmentStartDate]), FILTER ('Date2','Date2'[Date] = mStartDate ))

VAR ETest = CALCULATETABLE( VALUES('Outpatient'[AppointmentStartDate]), FILTER ('Date2','Date2'[Date] = mEndDate ))

both these variables return blank values. Not sure why this is happening as an Active relationship exists between the 'Date2'[Date] dimension and 'Outpatient'[AppointmentStartDate] fact.

I tried below code as well :

DEFINE MEASURE 'Outpatient'[Outpatient Visit Charge] =

VAR mStartDate = MIN('Date1[Date])

VAR mEndDate = MAX('Date1'[Date])

VAR STest = CALCULATETABLE( VALUES('Date2'[DATE]), FILTER ('Date2','Date2'[Date] = mStartDate ))

VAR ETest = CALCULATETABLE( VALUES('Date2'[DATE]), FILTER ('Date2','Date2'[Date] = mEndDate ))

VAR OutpatientVisitCharge = CALCULATE( SUM ('Outpatient'[VisitCharge]),

    'Outpatient'[IsFundable] = TRUE (),

     FILTER(ALL('Date2'), 'Date2'[Date] = DATE(RIGHT(STest,4),LEFT(STest,1),MID(STest,3,2)))) --- Testing to see if it works for a single date value which is the start date

RETURN  OutpatientVisitCharge 

EVALUATE ROW ("Outpatient Visit Charge",CALCULATE( 'Outpatient'[Outpatient Visit Charge],

   'Date1'[Financial Year Short Name] = "2023-24",

   'Date1'[Financial Month Name] = "March")

           )

Still got the same result i.e. blank. Can some one help figure out the problem in this calc resulting in blank value when the Fact table has data for all the dates and relationship also exists between Date2 and Outpatient.

Please note as the total charge will be sum of Inpatient and Outpatient data with both related to different dimensions hence I need to apply filter to second date dimension table based on first date dimension values.

And there is no relationship between Date1 and Date2 tables.

I could share the sample data but not sure how to do it as there is no option to attach an excel.

ScheduleKey	AppointmentStartDateId	AppointmentEndDateId	AppointmentStartTimeId	AppointmentEndTimeId	IsFundable	VisitCharge
82831374	1/03/2024	1/03/2024	10:00:00	10:15:00	1	0.0685
82890625	1/03/2024	1/03/2024	11:35:00	11:55:00	1	0.0428
81830845	1/03/2024	1/03/2024	10:00:00	10:30:00	1	0.0489
82658664	1/03/2024	1/03/2024	11:00:00	11:20:00	1	0.0428
82944604	1/03/2024	1/03/2024	0:00:00	0:00:00	1	0.0535
82801541	1/03/2024	1/03/2024	13:55:00	14:25:00	1	0.034776
82922163	1/03/2024	1/03/2024	0:00:00	0:00:00	1	0.0489
82347126	1/03/2024	1/03/2024	14:30:00	15:30:00	1	0.0628
81750638	19/03/2024	19/03/2024	10:30:00	11:00:00	1	0.0628

[RachnaVerma]

There are another two fact tables - Clinical Safety Incidents and Clinical Products and Services that have bi-directional relationship with Fact tables - Inpatient and Outpatient. The dimensions - Date1 and Date2 donot have bi-directional relationship. Can't change the design now as our new BI Manager wants it that way. And to be honest this bi-directional relationship has enabled great analytical visuals that were not possible before.

I had tried TREATAS but it didn't work, gave the same result i.e. BLANK. If it remove all the date related filters from the measure it does calculate Outpatient Visit Charge but sums up everything.

[lbendlin]

Usually fact tables should not be connected directly. They should be controlled by common dimension tables.

However - if the current setup works for your business scenario then so be it.

[lbendlin]

And these Fact tables have bi-directional relationship with both Inpatient and Outpatient Fact tables.

Design red flag...

if you want to transfer filters use TREATAS