SummarizeColumns() using mulitple fact tables

[Anonymous]

Hi guys

I require help with the below -

The setup:
I have consumed a model from Lotus notes, i.e. not a proper relational DB, with two fact tables that are related by [File_Number]:

• The granularity of fctCase is of [Group], i.e. multiple groups can work on a single [File_Number]. It has a population of 80 distinct [File_Number]'s.
• The granularity of fctTimeManagement is [Hours], [Employee] and [Date] (not shown above) for each [File Number]. It has a sample of 65 distinct [File_Number]'s.
• dimCase has a population of 80 distinct [File_Number]'s

The challenge:
I require a third table to be materialized made up of the below 5 columns.  However, it must contain all 80 [File_Number]/[Unit] combinations, and the 65 [Group]/[Date] combinations and their associated sum of hours . Essentially, I want to create a LEFT JOIN starting with all 80 [File_Number]'s.

Dimensions:
    dimCase[File_Number]
    ,fctCase[Unit]
    ,fctTimeManagement[Group]
    ,fctTimeManagement[Date]

Measure(s):
    SUM( fctTimeManagement[Hours] )

The problem:
I am having trouble including all 80 [File_Number]'s. Any time I add a dimension from fctTimeManagement it filters the distinct [File_Number]'s to 65.

For example, the below query results in 65 distinct [File_Number]'s. This makes sense because the dimensions are filtered with respect to "Hours_Sum":

Attempt01 = 
SUMMARIZECOLUMNS(
    'dimCase'[File_Number],
    'fctCase'[Unit],
    "Hours_Sum", SUM( fctTimeManagement[Hours] )
)

I need to include all 80 [File_Number]'s though, so I add a measure from a table with all numbers. This results in 80 distinct [File_Number]'s (getting closer...):

Attempt02 = 
SUMMARIZECOLUMNS(
    dimCase[File_Number]
    ,fctCase[Unit]
    ,"rc", COUNTROWS( dimCase ) // <-- added
    ,"Hours_Sum", SUM( fctTimeManagement[Hours] )
)

But as soon as I add a column from the fctTimeManagement table, it filters it back to 65 distinct [File_Number]'s:

Attempt03 = 
SUMMARIZECOLUMNS(
    dimCase[File_Number]
    ,fctCase[Unit]
    ,fctTimeManagement[Date] // <-- Added
    ,"rc", COUNTROWS( dimCase )
    ,"Hours_Sum", SUM( fctTimeManagement[Hours] )
)

I've tried other things like playing wtih bi-directional filtering, using IGNORE() or using SUMMARIZE with no luck.

Does anyone have a solution?

Thanks!

[v-ljerr-msft]

Hi @Anonymous,

Have you tried making the cross filter direction to Single between "dimCase" table and "fctTimeManagement" table?

According to your description above, I have tested it on my side, the measure "Attemp03" works when I make the cross filter direction to Single between "dimCase" table and "fctTimeManagement" table.

Regards

[Anonymous · Accepted solution]

Hi V

When I do that it returns a CROSS JOIN of the tables because it cannot propogate filter context.  Unfortunately that's not what I'm after (in my real dataset it blows the results out to about 1.2 billion rows).

I have come up with a solution through UNION, see below (I apologize for the size of the query, I don't have time right now to clean it up to match my example above).

TLDR; With the use of a simple helper column [timeEntryExists], I create the extract with the 65 [File_Numer]'s as above. I then union the results for the missing 15 [File_Number]'s (80 - 65).

EVALUATE
UNION (
    FILTER (
        SUMMARIZECOLUMNS (
            dimCase[timeEntryExists],				    // 0
            dimCase[File_Number],                                   // 1
            dimCase[File_Name],                                     // 2
            fctCase[Investigation_Type_Primary],                    // 3
            fctCase[EarliestOpeningDt],                             // 4
            fctCase[EarliestPhase],                                 // 5
            fctCase[Unit],                                          // 6
            fctCase[Unit_Team],                                     // 7
            fctCase[Outcome],                                       // 8
            fctCase[Closing_Date],                                  // 9
            tblSubject[showsect],                                   // 10
            tblSubject[SubjName],                                   // 11
            tblSubject[Settlement_Dt],                              // 12
            tblSubject[OrderFinal_Dt],                              // 13
            fctTimeManagement[Task(Clean)],                         // 14
            fctTimeManagement[Group(Clean)],                        // 15
            fctTimeManagement[Staff],                               // 16
            fctTimeManagement[JobTitle],                            // 17
            fctTimeManagement[Submitted],                           // 18
            fctTimeManagement[Date].[Year],                         // 19
            fctTimeManagement[Date].[Month],                        // 20
            "Hours", [Hours Sum]                                    // 21
        ),
        dimCase[timeEntryExists] = TRUE ()
    ),
    SELECTCOLUMNS ( // Re-arranges columns and removes [Cases] to assist with UNION appending
        FILTER (
            SUMMARIZECOLUMNS (
                dimCase[timeEntryExists],
                dimCase[File_Number],
                dimCase[File_Name],
                fctCase[Investigation_Type_Primary],
                fctCase[EarliestOpeningDt],
                fctCase[EarliestPhase],
                fctCase[Unit],
                fctCase[Unit_Team],
                fctCase[Outcome],
                fctCase[Closing_Date],
                tblSubject[showsect],
                tblSubject[SubjName],
                tblSubject[Settlement_Dt],
                tblSubject[OrderFinal_Dt],
                "Cases", [Cases]
            ),
            dimCase[timeEntryExists] = FALSE ()
        ),
        "timeEntryExists", [timeEntryExists],		            // 0
        "File_Number", [File_Number],                               // 1
        "File_Name", [File_Name],                                   // 2
        "Investigation_Type_Primary", [Investigation_Type_Primary], // 3
        "EarliestOpeningD]", [EarliestOpeningDt],                   // 4
        "EarliestPhase", [EarliestPhase],                           // 5
        "Unit", [Unit],                                             // 6
        "Unit_Team", [Unit_Team],                                   // 7
        "Outcome", [Outcome],                                       // 8
        "Closing_Date", [Closing_Date],                             // 9
        "showsect", [showsect],                                     // 10
        "SubjName", [SubjName],                                     // 11
        "Settlement_Dt", [Settlement_Dt],                           // 12
        "OrderFinal_Dt", [OrderFinal_Dt],                           // 13
        "Task(Clean)", BLANK (),                                    // 14
        "Group(Clean)", BLANK (),                                   // 15
        "Staff", BLANK (),                                          // 16
        "JobTitle", BLANK (),                                       // 17
        "Submitted", BLANK (),                                      // 18
        "Year", BLANK (),                                           // 19
        "Month", BLANK (),                                          // 20
        "Hours", BLANK ()                                           // 21
    )
)

[v-ljerr-msft]

Hi @Anonymous,

Nice solution! 

Could you accept your last reply as solution to help others who may have similar issue easily find the answer and close this thread?

Regards