Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I faced some issue in power bi direct query mode. I want to fetch the column value in run time calculations .
I am explaining the details below. Lets say..
I have three table
Fact.Table1 Dim.Table2 Fact.Table3
Id Id GroupId
Score GroupId TotalScore
PercentileScore
Now I want the fetch Max of Fact.Table3[percentileScore] ,If (fact.table1[score]>=fact.Table3[TotalScore]) on the basis of GroupId.
But In direct query mode I cant able to create calculate column, that's the reason I want to create measure instead of column. However, I cant able to create measure as it has require aggregate function for each column. For example: IF(Average(fact.Table1[score])>=Sum(Fact.Table3[TotalScore]),Max(fact.Table3[percentileScore]),0)
but I need to compare with average [score] with [TotalScore] ,not with sum or any aggregate function. Also I have tried Filters, Values instead of aggregate function.
Although I have created the dax formula, however as soon as when I drag and drop on the widget ,it's shows error.
Please suggest me what I'll do and how can I fetch the Max(Fact.Table3[percentile column]) on the basis of above conditions.
Hi MitaliDutta,
You can't do this in the normal data model.
Normally, you can't compare values from one table to another.
Solution is either you have to join all the tables and do the calculations or Use "Summarize" function in DAX to get the dataset for your requirement.
Further you can use this to implement this.
Regards,
Pradeep
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |