SUMMARIZECOLUMNS Alternative

[ChrisCross]

Hello all,

In my data model, besides the fact table, there is a target table, which contains one target value per date and dimension.

Facts:

Targets:

The target value should be weighted at denominators of the fact table.

I have tried different approaches to realize this. The only one that works so far is to add calculated columns in the fact table, which can then be divided in the measure.
For each day and dimension, I calculate the associated target value, which I multiply by the denominators:

Target Average = 
var relevantRows = FILTER(
                Targets, 
                    Targets[Date] = 'Facts'[Date] && 
                    Targets[Dimension] = 'Facts'[Dimension]
                )
var foo = SELECTCOLUMNS(relevantRows, "Target", [Target])
return 
foo

Target Absolut = Facts[Denominator] * Facts[Target Average]

Weight = IF(NOT(ISBLANK(Facts[Target Average])), Facts[Denominator])

The result is as expected and correct regardless of the choice of dimension:

I am very interested in a Measure-based solution. With SUMMARIZECOLUMNS I could display the correct value. However, this function has some limitations so it stops working as soon as the dimension or date is brought into the context/visual.

(the second image shows what happens when I activate the SUMMARIZECOLUMNS measure in the first visual. The error message appears that SUMMARIZECOLUMNS cannot be used in this context).

Target via Measure SUMMARIZECOLUMNS = 
VAR _table =
    ADDCOLUMNS (
        SUMMARIZECOLUMNS (
            'Date'[Date],
            'Dimension'[Dimension],
            "_weight", SUM ( 'Facts'[Denominator] ),
            "_target", MAX ( 'Targets'[Target] )
        ),
        "_target absolut", [_weight] * [_target]
    )
VAR _tableFiltered =
    FILTER ( _table, NOT ( ISBLANK ( [_target absolut] ) ) )
RETURN
    
DIVIDE (
    SUMX ( _tableFiltered, [_target absolut] ),
    SUMX ( _tableFiltered, [_weight] ),
    BLANK ()
)

As an alternative for this I have worked with SUMMARZIZE and ADDCOLUMNS, but fail to get the dimension and date into a virtual table. This results in an incorrect value as long as the dimension is not selected:

Target via Measure SUMMARIZE = 
var _table1 =
SUMMARIZE(
	'Date', 
	'Date'[Date],
	"_weight", SUMX(RELATEDTABLE('Facts'), 'Facts'[Denominator]), 
	"_target", MAXX(RELATEDTABLE('Targets'), 'Targets'[Target])
	)
	
var _table2 = 
ADDCOLUMNS(_table1,
	"_targetAbsolut", [_weight] * [_target]
	)
	
RETURN

DIVIDE(SUMX(_table2, [_targetAbsolut]), SUMX(_table2, [_weight]), BLANK())

I know that in the virtually created table, the dimension information is missing. But I have no idea how to implement this.

The requirement is to get the result from [Target via Columns] or [Target via Measure SUMMARIZECOLUMNS] without any additional calculated columns and not make any change in the relationships.

Does anyone have any idea about this?

Thanks in advance and kind regards
Chris

[Whitewater100]

Hi:

Would you consider to just merge your two tables joinging on Date & Dimension?  Hope this can be a easy way to accomplish. Thanks..

[daXtreme · Accepted solution]

// You're violating many Best Practices of
// DAX and do not follow the documentation
// of the functions, hence the problems
// you're experiencing. For instance, in the
// documentation of SUMMARIZECOLUMS
// (dax.guide/summarizecolumns) it's stated
// that this function cannot be used in measures
// as it does not effect context transition.

// This might be the code you want:
Target = 
VAR vTable =
    ADDCOLUMNS (
        SUMMARIZE (
            // This fact table must be connected
            // to 'Date' and 'Dimension' via
            // many to one. I don't know which
            // fact table does it but in a good
            // model a fact is connected to dims.
            // If you have 2 fact tables, say,
            // 'Facts' and 'Targets', then you have
            // to decide which one to use here.
            // Bear in mind that fact tables must
            // never be connected directly to each
            // other, only via dimensions. If this
            // is not the case, you're model must
            // be changed.
            FactTable,
            'Date'[Date],
            // I can't see any 'Dimension' table
            // in your post...
            'Dimension'[Dimension]
        ),
        // You put @ in front of a name of a
        // calculated column in a virtual table.
        // This is one of the DAX conventions.
        "@Target",
            CALCULATE(
                var vSum = SUM ( 'Facts'[Denominator] )
                var vTgt = MAX ( 'Targets'[Target] )
                return
                    vSum * vTgt
            ),
        "@Weight",
            CALCULATE( SUM ( 'Facts'[Denominator] ) )
    )
VAR vTableFiltered =
    FILTER (
        vTable, 
        NOT ISBLANK ( [@Target] )
    )
RETURN
    DIVIDE (
        SUMX ( vTableFiltered, [@Target] ),
        SUMX ( vTableFiltered, [@Weight] )
    )

[ChrisCross]

Hi @Whitewater100 

Thank you very much for your detailed answer. The DAX formula works.
The connections between the tables exist exactly as you described them. A 'dimension' table exists, however I had not mentioned it in the post:

I have not yet been able to use the SUMMARIZE function to create a virtual table that combines information from different fact tables. You have shown me how to do this. Thanks again!

Chris