Forum Discussion

Riyaz999's avatar
Riyaz999
Helper II
9 months ago
Solved

Different Matrix visuals on multiple reports using 1 FACT table arranged in key value pairs

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):  ...
Need Help
  • v-hashadapu's avatar
    v-hashadapu
    9 months ago

    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.

v-hashadapu's avatar
v-hashadapu
Community Support
9 months ago

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's avatar
Riyaz999
Helper II
9 months ago

I would say that the issue I had raised is solved but it was really in the comment where you had attached the file "Different Matrix visuals 2.pbix" in conjunction with this recent comment.  Do I click the "Accept as Solution" on the Message containing the file or the most recent response or can I click both without confusing anyone that there aren't two separate solutions?

If I were to add a second visual to an existing report would I be able to get an assist?  I know it probably requires a new topic, but it would be a lot of effort to re-create all the context contained in this topic into a brand new one.  

I will include the ask, and you can let me know if it can be answered in this topic or it is sufficiently involved that it requires a new topic.
I want to include a bar chart with Station on the y-axis (from ReportStation) and a dynamic x-axis.  With the existing knowledge that my fact table has key value pairs as would be the case for my bridge tables (such as ReportMetric), I would create a field parameter pulling in measures for various metrics in my fact table or ReportMetric table.  
i.e., 

mBCDgr =
CALCULATE(
    MAX('FireWeatherData'[Val]),
    FILTER('FireWeatherData',
        'FireWeatherData'[Metric] = "BC_Dgr"
    )
)


My matrix visual looks good but the bar chart has repeat entries for each station (notice size of scrollbar):

The field parameter I created is:

FWDMetricSlicer = {
    ("FFMC", NAMEOF('Measure'[mFFMC]), 0),
    ("DMC", NAMEOF('Measure'[mDMC]), 1),
    ("DC", NAMEOF('Measure'[mDC]), 2),
    ("ISI", NAMEOF('Measure'[mISI]), 3),
    ("BUI", NAMEOF('Measure'[mBUI]), 4),
    ("FWI", NAMEOF('Measure'[mFWI]), 5),
    ("DSR", NAMEOF('Measure'[mDSR]), 6),
    ("BC_Dgr", NAMEOF('Measure'[mBCDgr]), 7)
}
 
My slicer is based off of FWDMetricSlicer:

I assume that I would need a measure in the x-axis well that filters on the selected value of the slicer which I haven't worked out yet, but that still wouldn't resolved my repeated stations issue.  I am assuming that the repeats are for each datetime but there is only one time on any given day that the metrics that I wish to dyanamically display in this chart are even available and I have filtered my visual by top 1 by Latest Date and when the time is 12:00pm.
Based on the data in ReportStation, only 5 Stations should appear, just like the top table.  What I am seeing is all Stations multiple times.

Thanks in advance

  • v-hashadapu's avatar
    v-hashadapu
    Community Support
    9 months ago

    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.