Slow Dax with 9M rows

[onajourney]

I am building a dashboard to get the counts of active employees on a specific date. This has come with quite a few challenges. The original data set has 400K rows. Here is the look of the test data:

First, the user can get the breakdown by 13 different categories in two columns. I had to unpivot the columns for this which created 9 million rows. To get the second dynamic slicer,  I had to copy the query and keep just the unpivoted rows and have another table with 9 million rows and have a many to many relationship on the index like below. I had to copy the columns as the user can also use the slicer if they want to only see counts for a specific state. 

I have a date text filter with a measure to get the active employees using: 

Show or Hide =
VAR SelectedDate = IF(SELECTEDVALUE('As of Date'[Date]) = "Today", TODAY(), DATEVALUE(SELECTEDVALUE('As of Date'[Date])))
RETURN
IF (SelectedDate >= MIN(FirstDate) && SelectedDate <= MAX(LastDate),"Show","Hide")

This is my measure to get the counts of the active employees at that date 

Sum Filter 2 =

VAR tmptable = FILTER(Sheet 1, [Choose Show or Hide] = "Show" && 'Sheet1'[Status] = "Active")

return

COUNTAX(tmptable, 'sheet1'[Emp ID])

My questions are: 

• Is there a better way to model the data to make the dax faster? I tried to do 1 table of my base rows of 400K and 2 tables of 9M, but I wasnt able to put the unpivoted tables in the matrix because there was no relationship. 
• Is there a better way to write the dax? Is Calculate with filters or Keep filters faster?
• Is there a way to get the date filter to update other slicers? For instance, the company may be inactive on 9/1/20, but they are still listed in the slicer. If I click on it, it shows 0 headcount, but I would like to remove the ability to click on it.

Thank you! I appreciate the time. 

[PaulDBrown]

@onajourney 

When you say you have unpivoted a column, which column is it? (Is the table you posted the result after unpivoting or before?)

you should also create a date table with continuous dates covering the range of dates in you model.

can you share some sample (non-condidential) data? 

[onajourney]

@PaulDBrown 

https://www.dropbox.com/s/rxy2scwedp4zfv0/test%20data.pbix?dl=0

I created this test file that is the overall picture of my dashboard. Let me know if you are unable to download.

Right now, the date measure isn't working, but the logic is at any date, they can see the active count of employees by the detail they choose. In my dashboard, there are 13 columns unpivoted. All of the ones that the user  can choose to see the level of detail of. 

Thank you!

[onajourney]

This is the look of the temp dashboard with drop down slicers. It does not include the date slicer, but that is a crucial piece to my real dashboard.