After several hours of trial and error without a reasonable solution I guess it is time to get the BI community involved.
So I have this table called "Vacation" with different staff members in the first column and 2 more columns "Start" and "Begin" of their vacation dates.
First, I created another table called "Dates" to visualize the number of absent people for each date. This isn't the problem. I am now trying to list the names of the absent people in a table for a selected period . So I have this slicer "date" (based on the table "Date") and I am able to pick out the selected Start and End date via two measures:
I thought I could use these measures within a calculated column in my table "Vacation" to evaluate if a entry is within my time period or not.
By the way, the easiest way to do what you want is to have a bridge table that would join Mitarbeiter with all the individual dates when they were absent from work. Then, once you have it, it's dead easy to calculate for any period of time (say, day) how many of them were absent. How? Well, you'd connect your Dates to the bridge table on one field and the Mitarbeiter dimension (you have such a dimension, right?) on another and after selecting a date from Dates you'd just DISTINCTCOUNT the number of Mitarbeiter in the bridge table. Easy enough. Can't get any easier than that.
sry for my late response, but I am only able to work on this topic once in a while. And a big thank you for your replies to my topic, I learned a lot out of them!
So yes, I do have a proper date table (no gaps, unique values), a Budget table as dimension table (Each employee in a different row with some further information) and a fact table with the vacation dates. My problem is that my vacation table does only provide a period defined by start, duration and end. So there are no individual absent dates which I could connect to my Employee table.
I have 2 questions:
How do I create a table with all individual absent dates for each employee?
How can I create a measure returning true or false whether or not the conditions are met? Should I use the SWITCH or IF function? Could you provide an example?
Thanks in advance - Regards
You are absolutely right. This approach is not only incorrect. It will not work at all since tables in a model are never dynamic. They are static, so once calculated, they never change (until the next refresh is requested). On top of that, using the function FILTERS... well, I've been working with DAX for at least 6 years (hardcore development), and never had a need for this. Your values could be harvested - and in fact should be - by using the MAX and MIN functions. Your dates should always be real dates, never strings. Also, using measures to calculate columns? Not a good idea. Not only because once calculated they'll stay frozen, but also because such a practice can turn against you very quickly.
What you really need is a proper date table and a measure that for each individual Mitarbeiter will return True or False depending on whether the condition you're talking about is met or not. Once you have such a measure, you'll be able to calculate about anything about the absenteeism... (die Urlaube, so zu sagen:).
thanks for your answer! I am pretty new to PBI and havent attended a course yet. So please forgive me my naive thinking 😄 What do you mean by a proper date table? I have a date table
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.