Using RELATED DAX function in FACT table from SQLServer

[Riyaz999]

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 DAX function to utilize the Precision field in the Metric table in conjunction with the Value field in the FACT table but it gave me an error when doing so.

RoundedValMeasure = 
VAR Precision = RELATED('Metric'[Precision])

VAR PrecFormat = IF(VALUE(Precision) > 0, "." & REPT("0", Precision), "")

RETURN

   FORMAT(FireWeatherData[Value], "0" & PrecFormat)

This DAX Measure works properly when my FACT table is a local table in PowerBI but I get the following error when it is pulled in via SQL Server source as either a "Direct Query" or "Import".  The error I get is:
The column 'Metric[Precision]' either doesn't exist or doesn't have a relationship to any table available in the current context.

It does have a relationship, and I am setting up the Measure on the many side of the relationship.

Any assistance would be appreciated.

[Riyaz999]

Hello all,

if calculated columns don't play nice in a Direct Query connection, when using fields from a different table, is there another way to go?  i.e., Import mode or using measures instead of calculated columns?  My issue is that I have several metrics stored in a single FACT table and plan to pivot the metrics into columns of a matrix but each metric/column will require a different level of precision.  

I basically have some values that will require 0 decimal places (whole numbers/integers), some that require 1 decimal place and some that will require 2 decimal places.  I will ROUND the values to the required level of precision.

Any thoughts or suggestions as to how I may accomplish this?  I feel like business rules where metric precision is concerned should be coming from a table in PowerBI however, if forced to do so, I could create a separate table in the SQL Server Database that stores the precision of each tracked metric and then join to my data table before importing into PowerBI.  Then I would have the level of precision in my FACT table and I don't have to use the RELATED DAX function.  I think I will defer the approach to anyone more experienced than myself in PowerBI.

Regards,

Riyaz

[lbendlin]

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

[v-aatheeque]

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]

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]

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]

ReportsTable

ReportId	Report
1	Test1
2	Test2

Metrics Table

MetricId	Metric
1	DC
2	Dir
3	ISI
4	WSpd
5	BC_Dgr

ReportMetrics (Metric Column could be omitted to normalize table)

ReportId	MetricId	Metric	Ordinal
1	3	ISI	1
1	2	Dir	2
1	1	DC	3
1	5	BC_Dgr	4
2	4	WSpd	2
2	2	Dir	1
2	5	BC_Dgr	3

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

[v-aatheeque]

Hi @Riyaz999 

The main issue from trying to use a measure (like selectedReportId) inside a CALCULATETABLE() filter context, which does not evaluate as expected in a calculated table.

Ensure relationships between your tables linked properly (Reports[ReportId] → ReportMetrics[ReportId]), then create a new column in ReportMetrics that combines the report and the ordinal so each metric’s sort order is unique for its report.

After that, set your Metric column to sort by this new column. When you filter or slicer by report, Power BI will automatically show the right metrics in the right order no special DAX filtering table needed.

Hope this helps !!

[v-aatheeque]

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]

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]

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]

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

[v-aatheeque]

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 · Accepted solution]

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]

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]

Confirmed, the issue I raised here has been resolved.

[v-aatheeque]

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!

[lbendlin]

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

[v-aatheeque]

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.