topic Re: Calculation involving 3 fact tables, conditions and visualization in DAX Commands and Tips

Calculation involving 3 fact tables, conditions and visualization

SrMatto — Tue, 17 Dec 2024 00:04:13 GMT

Hi!

I am trying to achieve the following calculation:

Count all the programs that have been completed, considering the following conditions:

1. It should consider all the persons enrolled on that specific IdDate (defined in the slicers). i.e VALUES(DimDate[IdDate])

2. The completion should be on or after that IdDate. i.e FactCompletion: IdDate>=Min(FactEnrollment[IdDate]). FactEnrollment[Degree of Completion]="Complete"

3. The result should be presented in a table that has the Group and Category in which the Person was first considered, NOT when it was Completed. Also, a person can be in more than one Group during a IdDate. The info is in FactGroup

The data model is:

As an example, with these slicers:

These are the Persons enrolled in 20181 and 20231 with their corresponding Groups:

From this list, the ones that have Completed a Program are:

Thus, it should lead to this excpected result:

Note that a person might be counted more than once depending on the Rollup.

I have added a table in the PIBX, the fields are what I wish to have but the values are wrong

link to pbix

Thank you very much!

Re: Calculation involving 3 fact tables, conditions and visualization

lbendlin — Tue, 17 Dec 2024 02:00:48 GMT

Here is how I would refactor the data model. Avoid bridge tables, avoid joining fact tables, avoid bidirectionals.

Re: Calculation involving 3 fact tables, conditions and visualization

SrMatto — Tue, 17 Dec 2024 03:42:35 GMT

thank you for your reply, but it's nowhere near the expected result.
also, it doesn't solve the requirement of cross filtering 3 fact tables. (Or maybe i misunderstood your reply?)

Re: Calculation involving 3 fact tables, conditions and visualization

SrMatto — Fri, 20 Dec 2024 03:45:17 GMT

anyone have an idea on how to get the numbers?

Re: Calculation involving 3 fact tables, conditions and visualization

Anonymous — Wed, 25 Dec 2024 08:46:09 GMT

Hi SrMatto ,

I updated your sample pbix file(see the attachment), please check if that is what you want.

1. Delete the relationship between the table 'Bridge' and 'FactCompletion'

2. Create two measures as below to get the count of person ids which fulfill the conditions

Measure = VAR _selyear = ALLSELECTED ( 'DimDate'[year] ) VAR _semester = ALLSELECTED ( 'DimDate'[semester] ) VAR _category = ALLSELECTED ( 'DimGroup'[Category] ) VAR _program = SELECTEDVALUE ( 'Table'[Program] ) VAR _personids = CALCULATETABLE ( VALUES ( 'Bridge'[Person ID] ), FILTER ( ALLSELECTED ( 'Bridge' ), 'Bridge'[IdDate] IN ALLSELECTED ( 'DimDate'[IdDate] ) ) ) VAR _count = CALCULATE ( DISTINCTCOUNT ( 'FactCompletion'[Person ID] ), FILTER ( 'FactCompletion', 'FactCompletion'[Person ID] IN _personids && 'FactCompletion'[Degree of Completion] = "Complete" && 'FactCompletion'[IdDate] > MIN ( 'DimDate'[IdDate] ) && 'FactCompletion'[IdDate] > MIN ( 'DimDate'[IdDate] ) ) ) RETURN _count

Corrected Count = SUMX ( VALUES ( 'DimGroup'[Category] ), SUMX ( VALUES ( 'Table'[Program] ), [Measure] ) )

Best Regards

Re: Calculation involving 3 fact tables, conditions and visualization

SrMatto — Wed, 25 Dec 2024 22:44:46 GMT

Amazing.
Perfect timing for a christams gift!
Thank you so very much Anonymous