cant able to create measure in run time calculations on direct query mode

[Anonymous]

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.
 

[Anonymous]

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