Dynamic Column Part 2 - Modelling + DAX

Intro -

This blog is a continuation of Dynamic Column Part 1 - PQ. In this post, I will guide you through the next steps: building relationships and measures. I’ll also discuss the disadvantages of using Field Parameters for this functionality, providing a broader perspective on the approach.

Topic Starts

In the final step of the previous blog, we prepared three tables:

• Measure 1 Picker Table
• Measure 2 Picker Table
• Version DIM

Now, we’ll begin by building relationships and measures.

Step 1: Relationship Building

• Create a relationship between the Measure 1 Picker Table and Version DIM by connecting the Version column from Measure 1 Picker Table to the Version 1 column in Version DIM.
• Similarly, create a relationship between the Measure 2 Picker Table and Version DIM using the same columns.

Step 2: Actuals, Budget, Forecast Measures

In the sample data shared earlier, we have Actuals, Budget, and Forecast numbers as separate columns. Based on this structure, we can create individual measures for each scenario, such as:

• Actuals Measure
• Budget Measure
• Forecast Measure

ACT = CALCULATE(SUM(Sales[Actuals]))
BUD = CALCULATE(SUM(Sales[Budget]))
Apr FC = CALCULATE(SUM(Sales[Apr FC]))
Sep FC = CALCULATE(SUM(Sales[Sep FC]))
Dec FC = CALCULATE(SUM(Sales[Dec FC]))        

These foundational measures will be used to build the final dynamic measure, enabling the functionality discussed.

Step 3: Final Steps

• Add two slicers to your report, one each for version selection from the:

1. Measure 1 Picker Table
2. Measure 2 Picker Table

These slicers will allow users to dynamically select the versions they want to compare or analyze.

• Add the following columns from the Version DIM table into the Columns section of a matrix:

1. Section
2. Variance

• Use the DAX code below for your final measure to enable dynamic calculations:

Final = 
var _selected = SELECTEDVALUE('Version DIM'[Variance])
var _variance = SELECTEDVALUE('Version DIM'[Variance])
var _section = SELECTEDVALUE('Version DIM'[Section])
var _version1 = SELECTEDVALUE('Version DIM'[Version 1])
var _version2 = SELECTEDVALUE('Version DIM'[Version 2])
// 1&2 measure below are just here to give functionality to select different time frame for Version 1 and 2.
var _act1 =  CALCULATE([ACT], ALL('Measure 2 Picker'), ALL('Year 2'), ALL('Month 2'))
var _act2 =CALCULATE([ACT], ALL('Measure 1 Picker'), ALL('Year 1'), ALL('Month 1'))
var _bud1 = CALCULATE([BUD], ALL('Measure 2 Picker'), ALL('Year 2'), ALL('Month 2'))
var _bud2 =CALCULATE([BUD], ALL('Measure 1 Picker'), ALL('Year 1'), ALL('Month 1'))
var _sep1 =CALCULATE([Sep FC], ALL('Measure 2 Picker'), ALL('Year 2'), ALL('Month 2'))
var _sep2 =CALCULATE([Sep FC], ALL('Measure 1 Picker'), ALL('Year 1'), ALL('Month 1'))
var _dec1 =CALCULATE([Dec FC], ALL('Measure 2 Picker'), ALL('Year 2'), ALL('Month 2'))
var _dec2 =CALCULATE([Dec FC], ALL('Measure 1 Picker'), ALL('Year 1'), ALL('Month 1'))
var _apr1 =CALCULATE([Apr FC], ALL('Measure 2 Picker'), ALL('Year 2'), ALL('Month 2'))
var _apr2 =CALCULATE([Apr FC], ALL('Measure 1 Picker'), ALL('Year 1'), ALL('Month 1'))

var _switch = SWITCH(TRUE(),
                             _variance = "Act" && _section = "Measure 1",_act1,
                            _variance = "Act" && _section = "Measure 2",_act2,
                            _variance = "Bud" && _section = "Measure 1",_bud1,
                            _variance = "Bud" && _section = "Measure 2",_bud2,
                            _variance = "Sep FC" && _section = "Measure 1",_sep1,
                            _variance = "Sep FC" && _section = "Measure 2",_sep2,
                            _variance = "Dec FC" && _section = "Measure 1",_dec1,
                            _variance = "Dec FC" && _section = "Measure 2",_dec2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Act" &&_version2 = "Act" , _act1 - _act2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Act" &&_version2 = "Bud" , _act1 - _bud2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Act" &&_version2 = "Sep FC" , _act1 - _sep2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Act" &&_version2 = "Dec FC" , _act1 - _dec2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Act" &&_version2 = "Apr FC" , _act1 - _apr2,
                            
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Bud" &&_version2 = "Act" , _bud1 - _act2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Bud" &&_version2 = "Bud" , _bud1 - _bud2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Bud" &&_version2 = "Sep FC" , _bud1 - _sep2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Bud" &&_version2 = "Dec FC" , _bud1 - _dec2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Bud" &&_version2 = "Apr FC" , _bud1 - _apr2,

                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Sep FC" &&_version2 = "Act" , _sep1 - _act2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Sep FC" &&_version2 = "Bud" , _sep1 - _bud2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Sep FC" &&_version2 = "Sep FC" , _sep1 - _sep2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Sep FC" &&_version2 = "Dec FC" , _sep1 - _dec2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Sep FC" &&_version2 = "Apr FC" , _sep1 - _apr2,

                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Dec FC" &&_version2 = "Act" , _dec1 - _act2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Dec FC" &&_version2 = "Bud" , _dec1 - _bud2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Dec FC" &&_version2 = "Sep FC" , _dec1 - _sep2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Dec FC" &&_version2 = "Dec FC" , _dec1 - _dec2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Dec FC" &&_version2 = "Apr FC" , _dec1 - _apr2,

                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Apr FC" &&_version2 = "Bud" , _apr1 - _bud2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Apr FC" &&_version2 = "Sep FC" , _apr1 - _sep2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Apr FC" &&_version2 = "Dec FC" , _apr1 - _dec2,
                            NOT(_section) in {"Measure 1","Measure 2"} && _version1= "Apr FC" &&_version2 = "Apr FC" , _apr1 - _apr2
)
RETURN
_switch        

• Drag and drop the Final Measure into the Values section of the same matrix.

With this setup, the matrix will dynamically adjust based on the selections made in the slicers for both measures. It will not only display the selected measures but also calculate and showcase their variances, making it fully functional and interactive.

Conclusion

As you can see, the current setup allows the matrix to dynamically display three columns whose names change based on the Version selections. Now, let’s discuss why I chose not to use the Field Parameter approach for this solution.

Field Parameter Approach

Suppose I create a Field Parameter like the one below, maintaining a structure similar to the Version DIM table:

// I haven't made all the comparison scenario in the below Field Parameter Dax. 
Parameter = {
    ("Act", NAMEOF('1. Measures'[Act Version 1]), 0,"Act", "Act", "Act", "Section 1"),
    ("Act", NAMEOF('1. Measures'[Act Version 2]), 1,"Act", "Act", "Act", "Section 2"),
    ("Apr FC", NAMEOF('1. Measures'[Apr FC Version 1]), 2,"Apr FC", "Apr FC", "Apr FC", "Section 1"),
    ("Apr FC", NAMEOF('1. Measures'[Apr FC Version 2]), 3,"Apr FC", "Apr FC", "Apr FC", " Section 2"),
    ("Bud", NAMEOF('1. Measures'[Bud Version 1]), 4,"Bud", "Bud", "Bud", "Section 1"),
    ("Bud", NAMEOF('1. Measures'[Bud Version 2]), 5,"Bud", "Bud",  "Bud", "Section 2"),
    ("Dec FC", NAMEOF('1. Measures'[Dec FC Version 1]), 6,"Dec FC", "Dec FC", "Dec FC", "Section 1"),
    ("Dec FC", NAMEOF('1. Measures'[Dec FC Version 2]), 7,"Dec FC", "Dec FC", "Dec FC", "Section 2"),
    ("Sep FC", NAMEOF('1. Measures'[Sep FC Version 1]), 8,"Sep FC", "Sep FC", "Sep FC", "Section 1"),
    ("Sep FC", NAMEOF('1. Measures'[Sep FC Version 2]), 9,"Sep FC", "Sep FC", "Sep FC", "Section 2"),
    ("Act vs Act", NAMEOF('1. Measures'[Act vs Act]), 10,"Act vs Act" , "Act", "Act", "Section 1vs2"),
    ("Act vs Apr FC", NAMEOF('1. Measures'[Act vs Apr FC]), 11,"Act vs Apr FC", "Act", "Apr FC", "Section 1vs2"),
    ("Act vs Bud", NAMEOF('1. Measures'[Act vs Bud]), 12,"Act vs Bud", "Act", "Bud", "Section 1vs2"),
    ("Act vs Dec FC", NAMEOF('1. Measures'[Act vs Dec FC]), 13,"Act vs Dec FC", "Act", "Dec FC", "Section 1vs2"),
    ("Act vs Sep FC", NAMEOF('1. Measures'[Act vs Sep FC]), 14,"Act vs Sep FC", "Act", "Sep FC", "Section 1vs2")
}        

I then establish relationships similar to those used for Version DIM. However, when I drop the required columns into the matrix, the matrix shows unexpected behavior. It creates multiple sections, which is not what we want.

Possible Reason

I’m unsure of the exact cause of this behavior, but here’s what I suspect:

Although the Field Parameter table contains both the measures and the column headers, it does not treat the measures as intrinsic parts of the parameter table. Instead, it always references the table where the measure was originally defined.

This disconnect might lead to the creation of multiple sections in the matrix, as the parameter table cannot inherently unify these measures into a single seamless structure.

This limitation is one of the reason I opted for the Version DIM approach, as it offers greater control and avoids the fragmented behavior observed with Field Parameters.