cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChrisCross
Frequent Visitor

SUMMARIZECOLUMNS Alternative

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:

ChrisCross_0-1653042076756.png

Targets:

ChrisCross_1-1653042096944.png

 

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:

ChrisCross_2-1653043436433.png

 

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:

ChrisCross_3-1653043541245.png

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.

ChrisCross_4-1653043617490.pngChrisCross_5-1653043644096.png

(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:

ChrisCross_6-1653043792323.pngChrisCross_7-1653043814797.png

 

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

1 ACCEPTED SOLUTION
daXtreme
Resolver III
Resolver III

// 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] )
    )

View solution in original post

3 REPLIES 3
Whitewater100
Solution Sage
Solution Sage

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
Resolver III
Resolver III

// 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] )
    )

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:

ChrisCross_0-1653114204461.png

 

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

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors