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.
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
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.
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.
lol
analagous = analogousHi 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.