Forum Discussion

Riyaz999's avatar
Riyaz999
Icon for Helper II rankHelper II
1 year ago
Solved

Using RELATED DAX function in FACT table from SQLServer

Hello there,   I have a FACT table that is sourced from a SQL Server Database (FireWeatherData).  I have related this table to a local table in Power BI (Metric).  I have tried using the RELATED DA...
Need Help
  • Riyaz999's avatar
    Riyaz999
    1 year ago

    I was on the right track with:

    CALCULATETABLE(
        ADDCOLUMNS(
            'ReportMetric',
            "FieldName", LOOKUPVALUE('Field'[FieldName], 'Field'[FieldId],'ReportMetric'[MetricId])
        ),
        FILTER(ReportMetric, 'ReportMetric'[ReportId] = [seletectedReportId])
    )
     
    Unfortunately, I was missing a relationship between my dax table and the Reports table.
    Works now.  Thanks for everyone's help.
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

Consider using Calculation Groups, Visual Calculations, or Field Parameters.  They all support dynamic formatting.

v-aatheeque's avatar
v-aatheeque
Icon for Community Support rankCommunity Support
1 year ago

Hi Riyaz999 

 

Just following up to see if the suggestion shared by the lbendlin  using Calculation Groups, Visual Calculations, or Field Parameters for dynamic formatting helped resolve your issue.

 

If you're still facing challenges or exploring alternate approaches, feel free to share any updates.

We're here to help!

  • Riyaz999's avatar
    Riyaz999
    Icon for Helper II rankHelper II
    1 year ago

    Hi v-aatheeque,

     

    to be honest, I haven't figured out how to use Calculation Groups, Visual Calculations or Field Parameters for my situation.  I did some research online to learn how to use these suggestions but still don't know enough to be able to apply the knowledge to solve my issue.

    • v-aatheeque's avatar
      v-aatheeque
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi Riyaz999 

      In this scenario i suggest you to Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

      Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
             Please show the expected outcome based on the sample data you provided.

       

      How to provide sample data in the Power BI Forum - Microsoft Fabric Community

       

      • Riyaz999's avatar
        Riyaz999
        Icon for Helper II rankHelper II
        1 year ago

        ReportsTable

        ReportIdReport
        1Test1
        2Test2

         

        Metrics Table

        MetricIdMetric
        1DC
        2Dir
        3ISI
        4WSpd
        5BC_Dgr

         

        ReportMetrics (Metric Column could be omitted to normalize table)

        ReportIdMetricIdMetricOrdinal
        13ISI1
        12Dir2
        11DC3
        15BC_Dgr4
        24WSpd2
        22Dir1
        25BC_Dgr3

         

        If I select the Metric or MetricId column and attempt to sort by ordinal, I will get an error because PowerBI can't handle sorting when there is more than one occurrence of Metric with a different corresponding value for Ordinal. This scenario occurs because both reports Test1 and Test2 contain the fields Dir and BC_Dgr.

         

        I have come up with an idea but I can't quite get to work.  I store all 9 reports in the ReportMetrics table with all the metrics required for each of those 9 reports and the corresponding ordinal value to depict the order that the metrics will appear in the collumns of a matrix.

        1. Set up a page level filter on each report page/tab on the field:
        Reports[Report]

        for which I would have selected either Test1 or Test2.

        2. Create the following measures:

        selectedReport = SELECTEDVALUE('Reports'[Report])

        selectedReportId =
            CALCULATE(
                   MAX('Reports'[ReportId]), FILTER('Reports', 'Reports'[Report] = [selectedReport]
           )

        3. Create a table based on table measure function that essentially filters the ReportMetrics table to only contain the Metrics for the given selected Report.
        ReportMetricTable = 
        RETURN CALCULATETABLE(
            'ReportMetrics',
            'ReportMetrics'[ReportId] = [selectedReportId]
        )

        4. Then I go to table view and select the table I created, ReportMetricTable, and sort the Metric column by Ordinal.

        It works when I hard code the filter for ReportMetricTable to appears as one of the following scenarios:
        'ReportMetrics'[ReportId] = 1
        or
        'ReportMetrics'[ReportId] = 2

        but it doesn't seems to respond to either using the measure value or if I were to convert the dax measures to local variables.  I put cards on my visual to show my measure values with I change the selected value on the page level filter and they show up correctly.  i.e., if I select Test2 from the page level filter, 
        selectedReport shows up as Test2 and selectedReportId shows up as 2.

         

        With this approach, I still carry an extra table in terms of overhead but it is calculated on the fly where I don't need new tables for each report or have to manage multiple tables each time I need to update which metrics need to be added to a report or order of metrics changes.

        Hopefully, pasting the tables in this message works.

        Regards