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.
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 =
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.
