Conditionally showing Empty Values - Help Needed

[Schmidtmayer]

Hello Everyone 😃

Having some kind of issue right now and I am in need of help.
Our company started to use APIs to write Outlook data to our SQL databases, so that we can kinda connect our Outlook calendar to our company structure existing in our databases via PowerBI. Filter by Departement, by Contract Role, etc.

First of all, the model involved:

Some explanations are to be made:

FactAbwesenheiten:

The FactTable of this model. Containing all appointments of all our employees, in general, one entry per appointment
BV - internal employee number
Datum - date of the entry
ganztags - classification, if this appointment is all day long 
Kategorie - Category of the appointment
Privat - classification, if this appointment is private or not
Realdatensatz - classification, if this entry is based upon real data or is made up by calculation logic

[Hint: Before actually loading the data to PowerBI, I took the Product of all relevant date values (today til the end of next year), all employees, all categories, all private values, all All-Day-LongValues. After that, I used a LEFT JOIN in SQL to join in to real data. All combinations of values in that inner product having no entry within the actual data have Realdatensatz = 0, otherwise 1.]
Team - ID of the team of the employee (hint: there may be employees belonging to more than one team or employees belonging to no team)

Rest is Dimensions:
DimMitarbeiterDaten - All employee-related data like Age, Gender, etc.
Teams - Table of all Teams
Kategorien - Table of all Categories
DimDatumswerte - Date Table
Berechnungen - Table for Measures

Task is: Basically the report should show all management members and their all day appointments, given in a calendar view:

This is matrix. Rows are employee numbers, columns are dates. Dates having no appointments are treated via measure as 0, then turning background color and font color white via conditional formating.

The used measure is the following:
-- filtering the FactTable to all real data and all all daylong appointments

VAR __Zwischentabelle = FILTER(FactAbwesenheiten, FactAbwesenheiten[ganztags] = 1 && FactAbwesenheiten[Realdatensatz] = 1)

-- Select all distinct values of employee number and date from the last table

VAR __Zwischenauswahl = DISTINCT(SELECTCOLUMNS(__Zwischentabelle, "Mitarbeiter", FactAbwesenheiten[BV], "Datum", FactAbwesenheiten[Datum]))

-- return 0 in case the count of all combinations is blank, the count of all combinations otherwise

RETURN IF(COUNTX(__Zwischenauswahl, [Mitarbeiter]) = BLANK(), 0, COUNTX(__Zwischenauswahl, [Mitarbeiter]))

This works fine. As soon as I use the team filter, problems start:

All team members are displayed correctly, all persons not belonging to the teams are switched to zeros and are still displayed.
In case  no team filter is applied, showing empty values is okay. We want to check who might not enter their appointments to Outlook. In case a team filter is applied, all team members should be displayed (even in case of empty values), other person shall not be displayed. I have no idea how to do this.

As a work around, a filter for employees is added, which yields correct results (I think because the row entries of the matrix are taken from the employee table).

Help is needed.

[Schmidtmayer]

Thanks for the reply, I know there may be other tools doing it better 😃 Nevertheless, the question itself remained unanswered.

[lbendlin]

"Power BI kommt hier an seine Grenzen" - haette es nicht besser sagen koennen...

Wrong tool.