Forum Discussion
Different Matrix visuals on multiple reports using 1 FACT table arranged in key value pairs
Hi Riyaz999 , Thank you for reaching out to the Microsoft Community Forum.
The bar chart showed repeated stations because Power BI was pulling all records from the fact table instead of just the latest value for each station at noon. It wasn’t filtering down to a single record per station, so multiple entries appeared in the visual.
We fixed this by adjusting the measure logic to dynamically filter the fact table for the latest date per station and for 12:00 PM, ensuring only one value per station appears in the bar chart. To support your dynamic metric selection, we also added a FWDMetricSlicer table that works with a SWITCH-based measure. This lets the chart update automatically based on whichever metric you pick in the slicer.
Please see the attached .pbix file for your reference.
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.
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.
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.
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/FWDataFWId 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 ResultNoon 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,