Forum Discussion
Helper IISetting 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.
Community SupportHi 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.
Helper IIThanks 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 =
mSelectedReportId =
The 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.