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

 

sample : 

 

Thanks.

 

  • 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.

10 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    6 months ago

    Hii Easley06 

     

    In Microsoft Power BI, you can keep Region / Store / Product only at the top level and hide them for subsequent month columns by controlling visibility with ISINSCOPE() (or HASONEVALUE) inside your measures. The idea is to return BLANK for row headers when the matrix is expanded into month columns, so it visually behaves like one table.

     

    Net Sales Visible :=
IF (
    ISINSCOPE ( 'Date'[Month] ),
    [Net Sales],
    BLANK ()
)

     

  • 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

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    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.

  • krishnakanth240's avatar
    krishnakanth240
    Super User
    6 months ago

    Hi Easley06 

     

    Can you please provide the sample data of tables, as you are noticing the blank data for  the visual screenshot you shared. Thank You!

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    6 months ago

    Hi Easley06,

    We are writing to follow up and ask whether the issue has been resolved on your end. If it remains unresolved, please provide sample data that clearly demonstrates the problem or question in a structured (non-image) format so we can better understand and address it. Ensure the data is relevant, does not contain any sensitive information, and directly relates to the issue. Also include the expected outcome based on the provided example.

    If you have any further questions, please feel free to contact the Microsoft Fabric community.

    Thank you.