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

[v-hashadapu]

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

This happened because Power BI doesn’t allow a column to be sorted by another column when the same metric appears in multiple reports with different sort orders. In your case, a single metric (e.g., Rn_24) could belong to both Noon Fireweather and Hourly Precipitation, each with a unique Ordinal. That caused Power BI to throw the “Sort by another column” error since one MetricName had multiple possible Ordinal values.

To fix this, we created a unique SortKey in the ReportMetric table that combines the ReportId and the Ordinal (e.g., “1-1”, “2-1”, etc.). This ensures every report–metric combination has a distinct sorting value. We then used this SortKey to sort the MetricDisplay column instead of Ordinal, which keeps the proper order of metrics per report while avoiding the ambiguity error.

Finally, we added an extra page with a ReportName slicer, so you can now either view each report separately (like before) or use the slicer page to dynamically switch between Noon Fireweather and Hourly Precipitation on a single page, whichever works best for your workflow.

Please see the attached .pbix file for your reference.

[Riyaz999]

Hi again,

I applied my data pulled from SQL Server to the 2nd example you had provided and that worked for the Metrics, as seen in the screenshot, but when I create a ReportStation table analagous to the ReportMetric table it does not.
Using StationName from FACT table:

Adding ReportStation table:

Then when replacing StationName in Rows well with StationDisplay from ReportStation table my visual ends up being:

Note the lengthy scrollbar with repeated entries.  The first visual/matrix is closer to what I should see with the exception that some of the stations should be excluded and the order is incorrect.
If I were merely looking at ReportId=1 for ReportStation, it would appear as follows (excluding calculated columns):

ReportStationId	ReportId	StationId	Ordinal
1	1	2	1
2	1	3	2
3	1	6	3
4	1	5	4
5	1	19	5

FireWeatherStation

StationId	StationName
2	FWx 14G
3	FWx 31N
6	FWx North Basin
5	FWx Martins Gulch
19	FWx Chris Creek

My updated datamodel is as follows:

If you are able to help point me in the right direction with respect to the ReportStation setup, that should help me get over the finish line.  Any assistance would be appreciated and thanks again for all of you help thus far.

[Riyaz999]

lol
analagous = analogous

[v-hashadapu]

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

Your ReportStation visual showed duplicates cause the relationship between your fact table and ReportStation didn’t filter correctly by both StationId and ReportId. Power BI was matching all StationIds in the fact table against multiple entries in ReportStation, causing each station to repeat several times.

Make sure your ReportStation table contains unique combinations of ReportId and StationId, then connect it to FireWeatherData using both columns with bidirectional filtering enabled. Once that’s set, sort StationDisplay by Ordinal (or by a SortKey) and your visual will behave exactly like your metric visual.

[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,