Trying to create others as measure for slicer selection

DAX DAX Calculations
1

Hello,

I am trying to create a measure that counts others whenever user selects a department on the slicer.

I have the following tables:

Table1,
image

And then I have another table to show the department count
image

I have created an index between each of the tables with their ID and BldgCode and used that to create a relationship among the tables. Afterwards, I have tried to use the following measure to show counts of others.

Other Department =
VAR SelectedDept = SELECTEDVALUE(Table2[Department],“Sales”)

RETURN

COUNTAX(FILTER(ALL(‘Table2’),‘Table2’[Department]<>SelectedDept),[CountsID]).

image
image1056×470 17.1 KB
I am trying to create a visual like the image above where the right-sided visual would show the others (that are not selected). However, there is something incorrect with my measure and it is not taking in others into account. Can somebody please tell me how to correct it? I am attaching the sample file here.

Slicer_Others Sample.pbix (31.3 KB)

Thank you

2

@supergallagher25,

Before diving into the DAX here, it seems that there are some data modeling issues to address first. The way you’ve got the tables and the filtering set up doesn’t make a lot of sense to me. The first question I would ask is whether the Employee table is a fact table or dimension table? I’m inferring from your post that it’s the latter, which would be appropriate if the values for department assignment, building and/or full-time/part-time change frequently. If that’s the case I would set up a Department dimension table with the unique values for Department ID and Department Description that filters the Employee table. If those values do not change frequently, that I would consider merging all the information together into one Employee dimension table.

Once we get the data modeling issues resolved, I think the DAX will be much easier to address.

  • Brian
3

Hello Brian,

Thank you for your time. Unfortunately this is how the table shows up and my actual file it pretty big. I’m trying to create a measure that would filter out the selection on the slicer and calculate other values.

4

@supergallagher25,

Hmmm…so no ability to change the current data model?

I’ll see what I can do if we have to proceed as is. One question - am I interpreting your data correctly that currently all Sales people are part time and all Marketing people are full time?

  • Brian
5

Hi @supergallagher, we’ve noticed that no response has been received from you since the 8th of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

6

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!