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.
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.
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.
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]
)
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,
RHi 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.