CROSSFILTER and wrong Grand Total

[HobbyS]

I have to following tables:

Employees (Dim-Table):

Transactions (Fact-Table):

And the following relationship:

I would like to show the highest Rate of Employees, that is used in Transactions, grouped by Division. I get correct results for each Division, but the Grand Total shows the highest rate of any employee, although that employee is not part of any transaction. 

MaxRate: =CALCULATE(MAXX(Employees[Rate]);CROSSFILTER(Employees[PK_Emp];Transactions[FK_Emp];Both))

Result:

Thank you for your help in advance.

[HobbyS]

Thank you @MAwwad 

That is exactly what I was looking for. I guess CROSSFILTER establiches a bi-directional relationship in the form of a cross-join. So each record of "Employees" is included. Not like a "left-join" where you would only have Employees that have a corresponding record in "Transactions"?

[MAwwad · Accepted solution]

The reason you are getting the wrong Grand Total in your visual is because the calculation is not taking into account the fact that some employees may not have any transactions. Since you are using the Both direction in the CROSSFILTER function, it is returning the maximum rate of any employee in the Employees table regardless of whether they have transactions or not.

To fix this issue, you can modify your MaxRate calculation by using the FILTER function to only consider employees who have transactions:

MaxRate := CALCULATE( MAXX( FILTER( Employees, COUNTROWS(RELATEDTABLE(Transactions)) > 0 ), Employees[Rate] ), CROSSFILTER(Employees[PK_Emp], Transactions[FK_Emp], Both) )

This modified calculation first filters the Employees table to only include employees who have transactions by using the RELATEDTABLE function to get a table of transactions related to each employee, and then counting the number of rows in that table. The MAXX function is then applied to the filtered table to return the maximum rate of those employees. Finally, the CROSSFILTER function is used to ensure the calculation is performed in both directions between the Employees and Transactions tables.

With this modification, the MaxRate calculation should now return the correct maximum rate for each division, as well as for the Grand Total.