Different Matrix visuals on multiple reports using 1 FACT table arranged in key value pairs

[Riyaz999]

I have data coming from SQL Server using a direct query to poplulate my FACT table (FWData).  I created a few Dimension tables in Power BI Desktop to support my reports. (see diagram below):

FieldType - Differentiates Fields as either Metrics or Parameters

Field - Contains Metrics and Parameters (a metric and parameter could have same name by appearance (hidden character that is not a space) which is why I use ids that are not in my source FACT table).  

Report - contains id and name of a given report

ReportMetric - a bridge table with FK ReportId from Report table and FK MetricId linking to FieldId in Field table.  There is also an ordinal field used to sort metrics for a given report.  Metric is included from the parent as follows:

Metric = LOOKUPVALUE('Field'[FieldName], 'Field'[FieldId],'ReportMetric'[MetricId])

FWData - FACT table.  There is no Metric id in the source, just the name (Metric). Any relationships with FACT table are based on the metric where dimension tables use the respective ids.

Matrix Visual
Each matrix visual will have standard data in the Row well, i.e.,

Depending on the report, I would like a different set of keys (from key/value pair in FACT table) listed in the columns (in a specified order).  In my case, the key is Metric which would contain values such as BUI, FWI, Rn_24, Temp, Dir, etc.  Based on my model, I could get this field from multiple places but more on that later.

My values well looks like (I use a measure that gets the Precision value from the Fields table to round and display numeric values with a specific number of decimal places):

For these values, I have the following measure that will apply rounding and report a designated precision level for a given metric value as given by the precision column in the Field table.  The measure looks as follows:

mRoundedValue =

VAR Metric = SELECTEDVALUE('FWData'[Metric])

VAR Precision = LOOKUPVALUE('Field'[Precision], 'Field'[FieldName], Metric)

VAR PrecFormat = IF(VALUE(Precision) > 0, "." & REPT("0", Precision), "")

VAR Result = IF(ISBLANK(Precision), 'FWData'[mValue], FORMAT(ROUND('FWData'[mValue], Precision), "0" & PrecFormat))

RETURN Result

The measure above uses the following measure that is in the FACT table:

mValue = MAX('FWData'[Val])

If I pull the Metric into the column well from:

1. FACT table -> it returns all the fields including those I do not wish to display (could apply visual filtering but that is not the approach I wish to take)
2. Field dim table ->it returns only the fields I require but I can't sort the columns as there is no field to sort against (order would be different depending upon the report in which the fields will appear and a given field could appear in zero to many reports)
3. ReportMetric bridge table -> it returns only the fields I require (only because I made a many to many relation between ReportMetric and FWData) but I get the following error when I try to sort Metric by Ordinal:

I was not able to sort by ordinal because this table has more than one entry for a given Metric with a different ordinal value albeit the row is unique (a metric only appears a single time for a give report).

I have tried to show/hide metrics based on being present in the ReportMetric table to no avail.  Ideally, a given Matrix for a given report would show a subset of Stations and a subset of Metrics with each appearing in a prescribed order.  I would like to accomplish this with a single FilteredReportMetric table and single FilteredReportStation table.  Both would be dynamically calculated based on the Page Level filter for each Report page where I have selected the Report by name.

The issue I found is that Calculated tables are processed during data refresh and do not respond to slicer interactions or page level filters.  There is the option of hard coding the table to filter or the reportname or reportid, but then I have 9 additional FilteredReportStation tables and 9 additional FilteredReportMetric tables.

FilteredTableNoonFW =

//VAR _ReportId = LOOKUPVALUE('Report'[ReportId], 'Report'[ReportName], SELECTEDVALUE('Report'[ReportName]))

//RETURN

    SUMMARIZE(

        FILTER(

            'ReportMetric',

            'ReportMetric'[ReportId] = 1

        ),

        Field[FieldId],

        Field[FieldName],

        ReportMetric[Ordinal]

    )

Is there a way to just have a single FilteredReportMetric table and a single FilteredReportStation table?

[v-hashadapu]

Hi @Riyaz999 , Thank you for reaching out to the Microsoft Community Forum.

The reason it didn’t work for you earlier is because calculated tables in Power BI are static, they’re only evaluated during refresh, so they don’t react to slicers or page filters like Report[ReportName]. That’s why your FilteredReportMetric table never changed when you switched reports. What we did instead was use the existing ReportMetric bridge table dynamically.

We built a unique display column (MetricDisplay) that can be sorted by Ordinal and a measure that looks up the right metric values from your fact table (FWData) based on the selected report context. Then, by using a simple page filter on Report[ReportName], each page automatically shows the correct set of metrics in the right order, all with one model, no extra calculated tables needed.

Please see the attached .pbix file for your reference.

[Riyaz999]

This really works well for my application.  The only thing is I have 60 metrics in my reports and I was hoping to only store the MetricId in my ReportMetric table, not the MetricName too.  If I add measure to this table to lookup the value for the MetricName with the dax below, then I can't add this to the columns well of my matrix visual.  Is there any way around this or do I have to just manually add the MetricName to this table. Doing so would be error prone because I could inadvertently have a mismatch that would be hard to debug.

mReportMetricMetricName =

VAR _MetricId = SELECTEDVALUE('ReportMetric'[MetricId])

VAR _FieldName = LOOKUPVALUE('Field'[FieldName], 'Field'[FieldId], _MetricId)

RETURN _FieldName

[v-hashadapu]

Hi @Riyaz999 , Thank you for reaching out to the Microsoft Community Forum.

The reason your measure version doesn’t appear in the Columns well is because Power BI only allows real columns (not measures) as axis fields.

The easiest fix is to keep the MetricName column in the ReportMetric table but make it a calculated column using LOOKUPVALUE instead of manually typing values. That way, the names stay perfectly synchronized with your Field table, you can still sort by Ordinal and you don’t have to maintain or risk mismatches manually.

Example: MetricName =

LOOKUPVALUE(

    'Field'[FieldName],

    'Field'[FieldId],

    'ReportMetric'[MetricId]

)

[Riyaz999]

Hello again,

I was trying to set up a ReportStation table analgous to what we had accomplished with the ReportMetric table, so that I may configure which stations appear in which reports in which order.  I did manage to get it to work however in the process of getting there, I had realized that the data in each matrix is the same for each row/station.  It is reporting the same value, max over all stations, for each station line.

i.e., the underlying data has Station 1's FFMC value set to 24.7 and Station 2's set to 30.5 but the matrix shows both as 30.5 (the max value across all stations).

For the record, I am using the mRoundedValue_ByReportMetric that you had provided in the attached power bi file.

Regards,
R

[Riyaz999]

If I wanted to attach a sample Power BI file, how would I do that?

This is what my reports look like using two calculated metric tables namely FilteredReportMetricNoonFW and FilteredReportMetricHrlyPrecip where I have hard coded the following in each respective table:

'ReportMetric'[ReportId] = 1

'ReportMetric'[ReportId] = 3

where I would want a single table with the above line reading as follows:

'ReportMetric'[ReportId] = _ReportId

and then there would only be a single table for filtering report metrics.

[Riyaz999]

Please disregard the precipitation values for North Basin.  I clearly messed up when copying data from my SQL Server Direct query FACT table to a local Power BI table for the purposes of uploading here.

[v-hashadapu]

Hi @Riyaz999 , Thank you for reaching out to the Microsoft Community Forum.

Avoid calculated tables for filtering metrics dynamically because calculated tables are static and don't change with slicer or page filter selections. Instead, rely on the relationships between your existing tables such as Report, ReportMetric and Field and leverage the natural filtering that happens when a report is selected. This way, your metrics shown in visuals like a matrix will automatically reflect the selected report context.

To maintain the specific order of metrics in each report, create a DAX measure that fetches the appropriate Ordinal value for the currently selected report and metric. You can then use this measure to sort your matrix columns dynamically. This method keeps your model flexible, with only one set of dimension and bridge tables serving all report pages. Additionally, Power BI's field parameters feature can help users dynamically select and sort fields in visuals without complex calculated tables.

Use report readers to change visuals - Power BI | Microsoft Learn

Slicers in Power BI - Power BI | Microsoft Learn

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

Sort one column by another column in Power BI - Power BI | Microsoft Learn

As for uploading data, please refer to this: How to provide sample data in the Power BI Forum - Microsoft Fabric Community.

[Riyaz999]

Hi v-hashadapu,

thank you for your response.  Unfortunately, I think I need a little more direction.  If I were to simplify my data model as follows:

Field

FieldId	FieldName	Precision
1	BC_Dgr	0
2	FFMC	1
3	Rn_1	1
4	Prec_1	1
5	Rn_24	2

Report

ReportId	ReportName
1	Noon Fireweather
2	Hourly Precipitation

ReportMetric

MetricId	MetricName	ReportId	Ordinal
1	<See Measure below>	1	3
3		2	1
2		1	2
4		2	3
5		1	1
5		2	2

MetricName = LOOKUPVALUE('Field'[FieldName], 'Field'[FieldId],SELECTEDVALUE('ReportMetric'[MetricId]))

FACT Table/FWData

FWId	StationId	StationName	Date	Metric	Val	mVal
1	1	Station1	2025-10-25	BC_Dgr	1.0	Meas
2	1	Station1	2025-10-25	FFMC	24.7
3	1	Station1	2025-10-25	Rn_1	8.6
4	1	Station1	2025-10-25	Rn_24	8.6
5	1	Station1	2025-10-25	Prec_1	8.6

mVal = MAX('FWData'[Val])

In Measures Table

mRoundedValue =

VAR Metric = SELECTEDVALUE('FWData'[Metric])

VAR Precision = LOOKUPVALUE('Field'[Precision], 'Field'[FieldName], Metric)

VAR PrecFormat = IF(VALUE(Precision) > 0, "." & REPT("0", Precision), "")

VAR Result = IF(ISBLANK(Precision), 'FWData'[mValue], FORMAT(ROUND('FWData'[mValue], Precision), "0" & PrecFormat))

RETURN Result

Noon Fireweather Matrix

Rows : StationName, Date

Columns : 'ReportMetric'[Metric]

Values : mRoundedValue
Filters on this page : 'Report'[ReportName] (selected value is "Noon Fireweather")

Hourly Precipitation Matrix

Rows : StationName, Date

Columns : 'ReportMetric'[Metric]

Values : mRoundedValue
Filters on this page : 'Report'[ReportName] (selected value is "Hourly Precipitation")

May I get you to assist me with writing the DAX measure that fetches the appropriate Ordinal value for the currently selected report and metric and how I would use it to sort the columns of my matrix visuals?  For my application, I don't need to dynamically allow users to sort fields using field parameters.

Would that measure look something like this (in my FWData table):

Ordinal =

 LOOKUPVALUE(

    'ReportMetric'[Ordinal],

    'ReportMetric'[MetricId],

    'FireWeatherData'[mMetric],

    'ReportMetric'[ReportId],

    LOOKUPVALUE(

        'Report'[ReportId],

        'Report'[ReportName],

        SELECTEDVALUE('Report'[ReportName])

    )

 )

I can't seem to be able to sort by this measure.  I have tried putting the measure in the FWData table, then selecting the Metric field and then under column tools >> Sort by column and this measure doesn't appear.  I may have misunderstood your response.

Regards,