Forum Discussion

Riyaz999's avatar
Riyaz999
Helper II
1 year ago
Solved

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

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 sce...
Need Help
  • Riyaz999's avatar
    Riyaz999
    1 year ago

    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 question

     

    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.

lbendlin's avatar
lbendlin
Super User
1 year ago

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.