Forum Discussion

Easley06's avatar
Easley06
Helper II
6 months ago
Solved

Matrix table

Hi, Hi, is this possible to do in matrix table my current setup is two separate tables then hide the region store and product for the 2nd table. is there a way to have 1 only table for this   sa...
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    6 months ago

    Thankyou, rohit1991, cengizhanarslan, FBergamaschi, krishnakanth240, and techies for your responses.

    Hi Easley06,

    Thankyou for the update.

    As suggested by FBergamaschi, krishnakanth240, and techies, please provide sample data that clearly demonstrates your issue or query in a structured format (not as an image) to help us understand and resolve the matter. Ensure that the data is relevant, free from any sensitive information, and directly related to the issue. Additionally, please share the expected outcome based on the given example.

    Thank you.

cengizhanarslan's avatar
cengizhanarslan
Super User
6 months ago

1) Create a disconnected “Column Layout” table

You can do this in DAX (or Power Query). This version is easiest to understand:

 

A) Disconnected table for Metrics

Metrics =
DATATABLE (
    "Metric", STRING,
    {
        { "OTD" },
        { "NET SALES" },
        { "COST of sales" },
        { "gross" }
    }
)
 

B) Disconnected table for Months 

Months =
DISTINCT (
    SELECTCOLUMNS (
        'Date',
        "MonthStart", DATE ( YEAR('Date'[Date]), MONTH('Date'[Date]), 1 ),
        "MonthLabel", FORMAT ( 'Date'[Date], "MMM-yy" )
    )
)

 

C) Crossjoin to create the final matrix columns table

MatrixColumns =
CROSSJOIN (
    SELECTCOLUMNS ( Months, "MonthStart", [MonthStart], "MonthLabel", [MonthLabel] ),
    SELECTCOLUMNS ( Metrics, "Metric", Metrics[Metric] )
)

No relationships from MatrixColumns to your model.

 

2) Put fields into the Matrix

  • Rows: Region, Store, Product

  • Columns: MatrixColumns[MonthLabel] then MatrixColumns[Metric]

  • Values: the single measure below

 

3) Create one “dynamic value” measure (TREATAS + SWITCH)

Matrix Value =
VAR _MonthStart = SELECTEDVALUE ( MatrixColumns[MonthStart] )
VAR _Metric     = SELECTEDVALUE ( MatrixColumns[Metric] )
RETURN
SWITCH (
    TRUE(),
    _Metric = "OTD",
        CALCULATE ( [OTD], TREATAS ( { _MonthStart }, 'Date'[MonthStart] ) ),

    _Metric = "NET SALES",
        CALCULATE ( [Net Sales], TREATAS ( { _MonthStart }, 'Date'[MonthStart] ) ),

    _Metric = "COST of sales",
        CALCULATE ( [Cost of Sales], TREATAS ( { _MonthStart }, 'Date'[MonthStart] ) ),

    _Metric = "gross",
        CALCULATE ( [Gross], TREATAS ( { _MonthStart }, 'Date'[MonthStart] ) )
)

This forces the correct month filter without needing the month to come from your real Date table (because the month is coming from the disconnected table).

  • Easley06's avatar
    Easley06
    Helper II
    6 months ago

    Hi, 

     

    for the result im getting only blank values.. 

     

    • FBergamaschi's avatar
      FBergamaschi
      Super User
      6 months ago

      Hi Easley06 ,

      can you show a bit of detail of what you actually implemented (and resulted in these blanks)? There must be something going wrong with names, maybe? Or something else. We need to find out.

      Thanks

      If this helped, please consider giving kudos and mark as a solution

      @me in replies or I'll lose your thread

      Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

      Consider voting this Power BI idea

      Francesco Bergamaschi

      MBA, M.Eng, M.Econ, Professor of BI