Calculation involving 3 fact tables, conditions and visualization

[SrMatto]

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!

[SrMatto]

anyone have an idea on how to get the numbers?

[v-yiruan-msft · Accepted solution]

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

[SrMatto]

Amazing.
Perfect timing for a christams gift!
Thank you so very much @v-yiruan-msft 

[SrMatto]

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?)

[lbendlin]

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