Matrix table

[Easley06]

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]

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.

[v-pnaroju-msft]

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.

[techies]

Hi @Easley06, this seems doable, can you please share the sample pbix file if possible?

[krishnakanth240]

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!

[cengizhanarslan]

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]

Hi, 

for the result im getting only blank values.. 

[FBergamaschi]

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

[rohit1991]

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 ()
)