Forum Discussion
Remove groups and keep values
I have a simple table where I put Department, Number of Employees and Salary Expenses like below :
DeptID Number_of_Employees Salary_Expenses
1 159 100000
2 240 135000
3 55 78999
The DeptID is coming from the Department table
The Number_of_Employees is a measure based on the fact table HR_Anatlytics :
Number_of_Employees := DISTINCTCOUNT(HR_Anatlytics[EmployeeSK])
The Salary_Expenses is a measure based on the fact table HR_Anatlytics :
Salary_Expenses := SUM(HR_Anatlytics[Salary])
The relationship between the Department dimension and the HR_Anatlytics is one to many.
My expected output is like below, I want to remove the groups and keep values
Number_of_Employees Salary_Expenses
159 100000
240 135000
55 78999
Hi AmiraBedh,
Current power bi does not support using measure expressions as category/axis fields to expand other measure calculations. (measure expressions are calculated based on the row contexts so it required a category/group field to define the calculate ranges)
In my opinion, I think you need to use calculate column instead:
Number_of_Employees := CALCULATE ( DISTINCTCOUNT ( HR_Anatlytics[EmployeeSK] ), FILTER ( HR_Anatlytics, [DeptID] = EARLIER ( HR_Anatlytics[DeptID] ) ) )In addition, you can also try to create a new calculated table that used summary records:
NewTable = SELECTCOLUMNS ( SUMMARIZE ( HR_Anatlytics, [DeptID], "Number_of_Employees", DISTINCTCOUNT ( HR_Anatlytics[EmployeeSK] ), "Salary_Expenses", SUM ( HR_Anatlytics[Salary] ) ), "Number_of_Employees", [Number_of_Employees], "Salary_Expenses", [Salary_Expenses] )If the above formulas do not help, can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us quickly clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
5 Replies
AmiraBedh Number_of_Employees and Salary_Expenses are both measures. In DAX Measures are calculated based on what is available in the filter context. If the axis/filter context is removed, the measures will not evaluate.
The workaround is to create equivalent calculated columns, cause they can work as axis.
