Forum Discussion
Setting col order of Matrix for diff reports using differing metrics from single unpivoted FACT tbl
Thanks for everyone's help. I have resolved this issue in the following manner.
tblMetric
- MetricId
- MetricName
tblReportMetric- MetricId
- ReportId
- Ordinal
tblReport
- ReportId
- ReportName
DAXTblReportMetricFilteredByReport
- MetricName
- MetricId
- Ordinal
- ReportId
FACTTbl (SQLServer)
- Date
- Time
- MetricName
- StationName
- Value
tblMetric 1 To Many tblReportMetric
tblReport 1 To Many tblReportMetric
tblMetric 1 To Many DAXTblReportMetricFilteredByReport
tblReport 1 To Many DAXTblReportMetricFilteredByReport
DAXTblReportMetricFilteredByReport Many To Many FACTTbl
DAXTblReportMetricFilteredByReport =
CALCULATETABLE(ADDCOLUMNS('tblReportMetric',"MetricName", LOOKUPVALUE('tblMetric'[MetricName], 'tblMetric'[MetricId],'tblReportMetric'[MetricId])),FILTER('tblReportMetric', 'tblReportMetric'[ReportId] = [mSeletectedReportId]))mSelectedReportId =
CALCULATE(MAX('tblReport'[ReportId]), FILTER('tblReport', 'tblReport'[ReportName] = [mSeletectedReport]))mSeletectedReport =SELECTEDVALUE('tblReport'[ReportName])Page Filter based on 'tblReport'[ReportName] on each report page where a single report name is filtered corresponding to the report page in questionThe table DAXTblReportMetricFilteredByReport essentially gives me the ReportMetric table after applying the page level filter for the given report. I don't need to keep separate tables for each report as one is calculated on the fly for each report page giving me a list of the metrics I am interested in and in the order I require them to apper (Modeling tab, select column on DAX table, and sort by column ordinal).
It is a little complicated buy much easier to support moving forward. If I am missing something in my solution or more clarrification is required, feel free to respond with a question.
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
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" >> OrdinalError: 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
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
Hi Riyaz999 ,
Based on your scenario 4/5, inorder to achieve the required sorting, instead of directly sorting the plain Metric column, you can create a new calculated column that makes each value unique by combining the Metric with the ReportId. For example:
MetricUnique = Table[Metric] & " | R" & Table[ReportId]Next, create a sort key column that ensures the proper order based on ReportId and Ordinal:
SortKey = Table[ReportId] * 1000 + Table[Ordinal]
Once these columns are created, go to Column Tools, select MetricUnique, and choose Sort by column → SortKey. Finally, in your Matrix visual, use MetricUnique in place of Metric for the column headers. This way, the metrics will be displayed in the correct order as defined by the Ordinal within each ReportId.
Hope this helps.
Warm Regards
Prashanth Rao.
