Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Schmidtmayer
Helper I
Helper I

Conditionally showing Empty Values - Help Needed

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:

Schmidtmayer_0-1695715945894.png

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:

Schmidtmayer_1-1695716899114.png

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:


Schmidtmayer_2-1695717398108.png

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.





2 REPLIES 2
Schmidtmayer
Helper I
Helper I

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

lbendlin
Super User
Super User

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

 

Wrong tool.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors