Matrix Table - keeps on repeating

[sydney20]

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 🙂

[Anonymous · Accepted 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.

[sydney20]

Thanks so much Rico!!!! 🙂 

[amitchandak]

@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))