Forum Discussion

wwolak's avatar
wwolak
Frequent Visitor
5 months ago
Solved

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...
  • cengizhanarslan's avatar
    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] )
            )
    )