Setting col order of Matrix for diff reports using differing metrics from single unpivoted FACT tbl

[Riyaz999]

Hello,

I have a FACT table that is sourced from a SQL Server Database (FireWeatherData) and two local PowerBI tables, namely TempMetricOrder and FWMetricOrder. 

I have simplified my scenario for the purposes of this request for assistance and part of that simplification was to exclude the Reports table.  In the simplified PowerBI Report, I have two reports: 

1) Temperature Report

2) Fire Weather Report

Each report will have a single Matrix.  

Temperature Report

Rows: (Station, Date)

Columns: ('TempMetricOrder'[Metric])

Values: (Value)

Fire Weather Report

Rows: (Station, Date)

Columns: ('FWMetricOrder'[Metric])

Values: (Value)

I would like to combine the tables TempMetricOrder and FWMetricOrder into a single table but this will create a many to many relationship.  In a typical data modeling scenario, I would take the PK from FireWeatherData and from the "Report"MetricOrder table and place them in a table between the MetricOrder and FireWeatherData tables.  Unfortunately, the FireWeatherData table is in SQL Server and would have no knowledge of which metric should be included in which reports.  Moreover, since there are external processes that frequently update this data, the PK in the FireWeatherData table will change every hour.  I also don't know if I can have a composite key in my ReportMetricOrder table (instead of Metric name, using id and then add a calculated column (i.e., <ReportId.Metric> = 1.1, 1.3)).

The problem I am trying to solve is a manner to determine which fields/metrics should be included in a given report and the order in which they should be displayed.  My FireWeatherData table contains some 20 different metrics for a given set of stations and dates to satisfy 9 different reports.  Not all metric appear on all reports and the order in which metrics appear on a given report could differ based on which report we are dealing with. 

My current solution works but I have had to create a separate table for each report to track both the inclusion and order of a metric in a given report (seen in image above for two reports).  Similarly, I had to do the same for the relationship between stations and reports.  i.e., that is 18 tables where I think I should be able to represent this in two tables (one for metrics and one for stations). 

It may even be possible to combine the MetricOrder and StationOrder tables but then I would still need another lookup table to differentiate the type so I am okay with not combining Metrics and Stations into a single table.

Any ideas on how I may model the data to solve this problem?

[jaineshp]

Hey @Riyaz999,

You've got 18 tables doing what 3 tables should handle.

Simple Fix:

• Create one Reports table - ReportID, ReportName (Temperature, Fire Weather, etc.)
• Merge all MetricOrder tables into one - ReportID, Metric, SortOrder
• Keep your FireWeatherData as-is - it's fine living in SQL Server

Relationships:

• Reports → MetricOrder (1:Many on ReportID)
• MetricOrder → FireWeatherData (Many:Many on Metric)

For each Matrix:

• Filter by ReportID at page level
• Columns: MetricOrder[Metric], sorted by SortOrder
• Same rows/values you're using now

Why this works:

• No more duplicate tables per report
• Easy to add new reports (just add rows, not tables)
• SQL Server table stays untouched
• Metric names are stable identifiers

Quick DAX to merge existing tables:

            MetricOrder = UNION(
            ADDCOLUMNS(TempMetricOrder, "ReportID", 1),
            ADDCOLUMNS(FWMetricOrder, "ReportID", 2)
             )

Result: 18 tables → 3 tables, same functionality, way cleaner model.

Been there with over-normalized models - this approach has saved me headaches on similar projects.

Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

Best Regards,
Jainesh Poojara | Power BI Developer

[Riyaz999]

Thanks for your response Jainesh.

I'm still not quite sure how the Matrix columns get sorted.  In my previous version with 9 Metric tables (and 9 station tables) when I sort Metric by Ordinal, there was only ever one ordinal value corresponding to each metric.  In the proposed solution where all 9 MetricOrder tables are merged into a single table, and there is a metric that spans multiple reports (i.e., there is more than one row in the MetricOrder table with same Metric) sorting will not work in the manner I tried.

The manner in which I sort is as follows:
"Table View" >> select the "Metric" column >> "Column Tools" menu >> select "Sort by column" >> Ordinal

Error: Sort by another column
Description: We can't sort the 'Metric' column by 'Ordinal'. There can't be more than one value in 'Ordinal' for the same value in 'Metric'.  Please choose a different column for sorting or update the data in 'Ordinal'.

This is the whole reason that I has split up the table in the first place.

It is entirely possible that I don't fully understand your proposed solution as I have to infer how to sort columns in a Matrix from your line above:

• Columns: MetricOrder[Metric], sorted by SortOrder

and your intent for me to sort the columns of the matrix in an entirely different manner.

Although the report does filter out the columns I don't want as per specified in the MatrixOrder table for a given report, the columns appear in alphabetical order, and not in the order specified:

I hope this explanation clarifies the issue I am trying to resolve.  In favour of the columns appearing in the order:

BC_Dgr

DC

Dir

ISI

WSpd

I want them to appear as:

ISI

DC

Dir

WSpd

BC_Dgr

as speficied in my MetricOrder table for ReportId = 2.

Best Regards,

Riyaz

[Riyaz999]

Hi @jaineshp,

are you able to provide more direction with respect to how one can sort MetricOrder[Metric] by SortOrder in the design approach you had proposed in the context of the data restrictions I have (i.e., more than one row containing the same metric with different ordinal/sort order values on account of appearing in multiple reports).

Thanks in advance

[lbendlin]

As long as your "metrics"  don't change frequently you can use a reference table that lists all the "metrics"  names, and then create a single measure that reads the "metrics"  value from the filter context and calculates the appropriate value.  That reference table can also include a sort order if desired.  Or you could use Field Parameters.