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.
Hello,
I’m trying to learn DAX expression using this employee model
I would like to know if there is any way to create the below report (as a single report), where the same salary column from employees is aggregated individually based on the relationships between the tables,
or Is this even possible?
| Employee Id | | | Emp Salary | | | Region ID | | | Region Level Salaries | | | Job ID | | | Job Level Salaries | |
| 100 | | | 24000 | | | 2 | | | 248200 | | | 04 | | | 24000 | |
| 101 | | | 17000 | | | 2 | | | 248200 | | | 05 | | | 34000 | |
| 102 | | | 17000 | | | 2 | | | 248200 | | | 05 | | | 34000 | |
| 145 | | | 14000 | | | 1 | | | 74200 | | | 15 | | | 27500 | |
| 146 | | | 13500 | | | 1 | | | 74200 | | | 15 | | | 27500 | |
| 201 | | | 13000 | | | 2 | | | 248200 | | | 10 | | | 13000 | |
| 108 | | | 12000 | | | 2 | | | 248200 | | | 07 | | | 12000 | |
Measures (Region Level Salaries, Job Level Salaries)
Solved! Go to Solution.
Hi @Anonymous ,
Try to use this measure:
Measure = IF(ISBLANK(SUM(employees[Salary])),BLANK(),[Your Sales by Region Measure])
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
I think what @amitchandak provided has solved your problem, right?
Best Regards,
Icey
Hi @Icey
Using the above DAX expression I created a measure (Salary by region) which partially solved the problem
Salary by region: calculate(sum(employees[salary]), allexcept(employees,regions, employees[salary]))
I understood ALLEXCEPT() -> removes all the filters applied on the table except for mentioned parameters
When I use this measure in the report this is the result
| employee_id | | | salary | | | region_id | | | Salary by region | |
| 100 | | | 1 | | | 74200 | | |
| 101 | | | 1 | | | 74200 | | |
| 102 | | | 1 | | | 74200 | | |
| 145 | | | 14000 | | | 1 | | | 74200 | |
| 146 | | | 13500 | | | 1 | | | 74200 | |
| 176 | | | 8600 | | | 1 | | | 74200 | |
| 100 | | | 24000 | | | 2 | | | 248200 | |
| 101 | | | 17000 | | | 2 | | | 248200 | |
| 102 | | | 17000 | | | 2 | | | 248200 | |
| 145 | | | 2 | | | 248200 | | |
| 146 | | | 2 | | | 248200 | | |
| 176 | | | 2 | | | 248200 | |
But I need the below output with filters applied on all other columns too, you can see the model relationships in my first question
| employee_id | | | salary | | | region_id | | | Salary by region | |
| 145 | | | 14000 | | | 1 | | | 74200 | |
| 146 | | | 13500 | | | 1 | | | 74200 | |
| 176 | | | 8600 | | | 1 | | | 74200 | |
| 100 | | | 24000 | | | 2 | | | 248200 | |
| 101 | | | 17000 | | | 2 | | | 248200 | |
| 102 | | | 17000 | | | 2 | | | 248200 | |
Is this even possible...
Thanks
Seethend
Hi @Anonymous ,
Try to use this measure:
Measure = IF(ISBLANK(SUM(employees[Salary])),BLANK(),[Your Sales by Region Measure])
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you @Icey & @amitchandak
These measures worked as expected
Salary by Region = calculate(sum(Employees[salary]), allexcept(employees, regions[region_id]))
Measure = IF(ISBLANK(SUM(employees[Salary])),BLANK(),[Salary by Region])
Thanks
Seethend
@Anonymous ,
Did not get it try like
calculate(sum(Table[salary]), allexcept(Table[Region_id]))
refer
https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |