Forum Discussion
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 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.
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.
18 Replies
Calculated columns in a Direct Query connection need to come from the same table row, they cannot use fields from other tables.
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.
-
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
Consider using Calculation Groups, Visual Calculations, or Field Parameters. They all support dynamic formatting.
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!
