Forum Discussion
wwolak
5 months agoFrequent Visitor
Matrix with dynamic values
Hi, I want to create a matrix as follows: I'd like the values for Current Stock, Safety Stock and Available Stock to change dynamically, based on unit selected in a slicer (two options pcs an...
- 5 months ago
1) Create a disconnected table for the matrix columns
Matrix Columns = VAR FixedCols = DATATABLE ( "ColType", STRING, "Label", STRING, "MonthStart", DATE, { { "CURRENT", "Current Stock", BLANK() }, { "SAFETY", "Safety Stock", BLANK() } } ) VAR MonthCols = SELECTCOLUMNS ( VALUES ( 'Date'[MonthStart] ), -- use your month start/date key "ColType", "MONTH", "Label", FORMAT ( 'Date'[MonthStart], "yyyy-MM" ), "MonthStart", 'Date'[MonthStart] ) RETURN UNION ( FixedCols, MonthCols )Then sort Matrix Columns[Label] by Matrix Columns[MonthStart] (and keep fixed ones first with an additional sort column if needed).
2) Use THIS in the Matrix
-
Rows: Region, Country, Material (your dims)
-
Columns: Matrix Columns[Label]
-
Values: a single measure like below
3) One measure that switches logic per column
Matrix Stock Value = VAR _type = SELECTEDVALUE ( 'Matrix Columns'[ColType] ) VAR _month = SELECTEDVALUE ( 'Matrix Columns'[MonthStart] ) RETURN SWITCH ( _type, "CURRENT", CALCULATE ( [Current Stock], REMOVEFILTERS ( 'Date' ) ), "SAFETY", CALCULATE ( [Safety Stock], REMOVEFILTERS ( 'Date' ) ), "MONTH", CALCULATE ( [Available Stock], TREATAS ( { _month }, 'Date'[MonthStart] ) ) ) -
cengizhanarslan
5 months agoSuper User
1) Create a disconnected table for the matrix columns
Matrix Columns =
VAR FixedCols =
DATATABLE (
"ColType", STRING,
"Label", STRING,
"MonthStart", DATE,
{
{ "CURRENT", "Current Stock", BLANK() },
{ "SAFETY", "Safety Stock", BLANK() }
}
)
VAR MonthCols =
SELECTCOLUMNS (
VALUES ( 'Date'[MonthStart] ), -- use your month start/date key
"ColType", "MONTH",
"Label", FORMAT ( 'Date'[MonthStart], "yyyy-MM" ),
"MonthStart", 'Date'[MonthStart]
)
RETURN
UNION ( FixedCols, MonthCols )
Then sort Matrix Columns[Label] by Matrix Columns[MonthStart] (and keep fixed ones first with an additional sort column if needed).
2) Use THIS in the Matrix
-
Rows: Region, Country, Material (your dims)
-
Columns: Matrix Columns[Label]
-
Values: a single measure like below
3) One measure that switches logic per column
Matrix Stock Value =
VAR _type = SELECTEDVALUE ( 'Matrix Columns'[ColType] )
VAR _month = SELECTEDVALUE ( 'Matrix Columns'[MonthStart] )
RETURN
SWITCH (
_type,
"CURRENT",
CALCULATE ( [Current Stock], REMOVEFILTERS ( 'Date' ) ),
"SAFETY",
CALCULATE ( [Safety Stock], REMOVEFILTERS ( 'Date' ) ),
"MONTH",
CALCULATE (
[Available Stock],
TREATAS ( { _month }, 'Date'[MonthStart] )
)
)