Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Solution Sage
Solution Sage

// 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
Solution Sage
Solution Sage

// 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors