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

Calculated columns in a Direct Query connection need to come from the same table row, they cannot use fields from other tables. 

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

    Hi Riyaz999 ,

    lbendlin Thanks for prompt response , ln addition to that let me add some more insights :

    • DirectQuery mode has restrictions on calculated columns and measures.

    • When using DAX with a FACT table from SQL Server:

      • Measures may have limited access to related tables compared to Import mode.

    • To ensure accurate calculations:

      • Check that the relationship between the FACT table and Metric table is properly established.

      • Make sure the relationship is active and allows cross-table referencing.