Forum Discussion

Riyaz999's avatar
Riyaz999
Helper 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
    11 months 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.
v-aatheeque's avatar
v-aatheeque
Community Support
1 year ago

Hi  Riyaz999 
 

Just checking in on your query regarding using selectedReportId inside CALCULATETABLE() and sorting metrics per report.

As mentioned earlier, ensuring the relationship between Reports and ReportMetrics is correct, then creating a combined sort column in ReportMetrics, should resolve the ordering issue without needing a special DAX filtering table.

 

Were you able to try this approach?

 

v-aatheeque's avatar
v-aatheeque
Community Support
1 year ago

Hi Riyaz999 

Following up on your question about using selectedReportId in  CALCULATETABLE() and sorting metrics by report.
As suggested earlier, verifying the Reports–ReportMetrics relationship and adding a combined sort column in ReportMetrics should address the ordering problem without requiring an additional DAX filtering table.

Have you had a chance to test this solution?


If we don’t hear back, we may close this thread in line with our community guidelines, but you’re always welcome to post a new query anytime.

Thank you for being part of the Microsoft Fabric Community!

 

  • Riyaz999's avatar
    Riyaz999
    Helper II
    11 months ago

    Hi v-aatheeque,

    sorry for the delayed response.  I was on vacation.  I had tried this approach previously but PowerBi will not allow me to sort the MetricId by a CalculatedOrdinal field because there will be more than one occurence of MetricId with a different CalculateOrdinal value.

    i.e. CalculatedOrdinal = ReportId + (Ordinal / 100)

    For the record, I have got the relationship between Reports–ReportMetrics set correctly:

    I have tried calculating my measure for ReportMetricTable multiple ways such as shown above:
    ReportMetricTable = 
    RETURN CALCULATETABLE(
        'ReportMetrics',
        'ReportMetrics'[ReportId] = [selectedReportId]
    )

     

    as well as:

    SUMMARIZE(
        FILTER(ReportMetric, 'ReportMetric'[ReportId] = [seletectedReportId]),
        ReportMetric[MetricId],
        'Field'[FieldName],
        ReportMetric[Ordinal],
        "Report", [seletectedReportId]
    )

     

    and:

    CALCULATETABLE(
        ADDCOLUMNS(
            'ReportMetric',
            "FieldName", LOOKUPVALUE('Field'[FieldName], 'Field'[FieldId],'ReportMetric'[MetricId])
        ),
        FILTER(ReportMetric, 'ReportMetric'[ReportId] = [seletectedReportId])
    )


    and: 
    creating a filtered table first with: 

    TempTable = FILTER('ReportMetric', 'ReportMetric'[ReportId] = [seletectedReportId])
     
    followed by:
    CALCULATETABLE(
        TempTable,
        ADDCOLUMNS(
            'TempTable',
            "FieldName", LOOKUPVALUE('Field'[FieldName], 'Field'[FieldId],'TempTable '[MetricId])
        )
    )

    No matter how I have tried, ReportMetricTable always seems to include rows with ReportId not filtered for the page value filter given by the Measure seletectedReportId.

    It is noteworthy to mention that I have a many to many relationship between ReportMetricTable and my FACT table containing all the data used in each report.
  • lbendlin's avatar
    lbendlin
    Super User
    11 months ago

    RELATED only works in a fact table when the relationship is *:1

  • v-aatheeque's avatar
    v-aatheeque
    Community Support
    11 months ago

    Hi Riyaz999 

    Just following up on the issue you raised about sorting MetricId by your CalculatedOrdinal field and the challenges with filtering ReportMetricTable. 

    Were you able to try out the suggestions shared by lbendlin (like the note about RELATED only working in many:one relationships)?

  • Riyaz999's avatar
    Riyaz999
    Helper II
    11 months 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.
  • v-aatheeque's avatar
    v-aatheeque
    Community Support
    11 months ago

    Hi Riyaz999 

    Glad to hear the earlier approach helped resolve your issue once you added the missing relationship.

    Could you confirm if everything is now working smoothly with your DAX table and reports, or if you still need any further assistance?

  • Riyaz999's avatar
    Riyaz999
    Helper II
    11 months ago

    Confirmed, the issue I raised here has been resolved.

  • v-aatheeque's avatar
    v-aatheeque
    Community Support
    11 months ago

    Hi Riyaz999 

    We’re glad to hear that your issue has been resolved! Please continue to use the Microsoft Fabric Community platform whenever you have questions.

     

    If the responses above helped address your query, kindly click Accept Answer , If you have any further queries, feel free to reach out.

     

    Thank you for being part of the Microsoft Fabric Community!