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.
mReportMetricMetricName =VAR _MetricId = SELECTEDVALUE('ReportMetric'[MetricId])VAR _FieldName = LOOKUPVALUE('Field'[FieldName], 'Field'[FieldId], _MetricId)RETURN _FieldNameHi 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,
R
