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! Would appreciate insights on where my model is going wrong.
I have these tables:
(1) Base headcount of employees w/c I am planning to use as dimension
(2) Data that would be used for the measures - this table has a person identifier that could be linked to table 1
(3) Dimension table for Region, Business Unit and Country
Basically, the stakeholder would want a visual and table that shows how many employees meets the metrics and how many are not. So the table 1 have a mapping of region and business unit per employee w/c was then connected to table 3.
Now, the issue is the matrix table. Since they would want as well the list of workers that does not have data in table 2, I have added +0 at the latter part of the DAX that simply count the rows, sum some data, etc. What happens is that, if I visualize it as a table: Region - Business Unit - Worker, the worker appears to ALL regions and BUs w/c should not be the case.
Any insights on where did I went wrong? Any help would be appreciated 🙂
Solved! Go to Solution.
Hi @sydney20 ,
I suggest you to try if function to remove the repeating. My Sample is as below.
Data (2):
Employee (1):
Business (3):
Relationship:
When I use measure+0 , I will get the same result like yours. The visual will keep repeating for all employee and business units.
Try this code.
Measure =
VAR _COMBINE = COMBINEVALUES("-",MAX(Employee[Employee]),MAX('Business Unit'[Business Unit]))
VAR _LIST = SELECTCOLUMNS(Employee,"COMBINELIST",COMBINEVALUES("-",Employee[Employee],Employee[Business Unit]))
VAR _RESULT = CALCULATE(SUM('Table'[Value])+0,VALUES(Employee[Employee]))
RETURN
IF(_COMBINE IN _LIST, _RESULT, BLANK())
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @sydney20 ,
I suggest you to try if function to remove the repeating. My Sample is as below.
Data (2):
Employee (1):
Business (3):
Relationship:
When I use measure+0 , I will get the same result like yours. The visual will keep repeating for all employee and business units.
Try this code.
Measure =
VAR _COMBINE = COMBINEVALUES("-",MAX(Employee[Employee]),MAX('Business Unit'[Business Unit]))
VAR _LIST = SELECTCOLUMNS(Employee,"COMBINELIST",COMBINEVALUES("-",Employee[Employee],Employee[Business Unit]))
VAR _RESULT = CALCULATE(SUM('Table'[Value])+0,VALUES(Employee[Employee]))
RETURN
IF(_COMBINE IN _LIST, _RESULT, BLANK())
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks so much Rico!!!! 🙂
@sydney20 , That is because of +0
In this case I have forced filter again to get only between for + 0
0 between range
Measure = var _1= SUM(Opportunity[Opportunity count]) +0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |