topic Re: Using Measures to calculate a column in DAX Commands and Tips

Using Measures to calculate a column

KimStahl94 — Wed, 15 Sep 2021 13:18:45 GMT

Hi guys,

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:

SlicerStart = FIRSTNONBLANK ( FILTERS(Dates[Date] ) , "" )

SlicerEnd = LASTNONBLANK ( FILTERS ( Dates[Date] ) , "" )

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.

AbsentSelectedPeriod =

VAR CurrentStart = Vacation[Beginn]

VAR CurrentEnd = Vacation[Ende]

VAR SelectedStart = [SlicerStart]

VAR SelectedEnd = [SlicerEnd]

RETURN

IF ( SelectedStart <= CurrentStart && SelectedEnd >= CurrentEnd , "absent within period" , BLANK () )

I would then use this columns to filter my table by "absent within period". This expression is just an example and would only cover vacations which start and end dates are within the selected period. There are more cases to cover but I guess my overall approach is wrong. Feel free to ask for more information due to this being my first post and I do not know how much information is needed. Thanks in advance for any tips!

Regards

KimStahl94

Re: Using Measures to calculate a column

Anonymous — Wed, 15 Sep 2021 14:07:29 GMT

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:).

Re: Using Measures to calculate a column

KimStahl94 — Wed, 15 Sep 2021 14:46:03 GMT

Hello Daxer,

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

Date = CALENDARAUTO ( )

Pretty basic but maybe proper enough? Could you maybe provide me with an example for a measure? I have created basic ones but I guess this could be a bit more complex.

Regards

KimStahl94

Re: Using Measures to calculate a column

Anonymous — Wed, 15 Sep 2021 15:19:16 GMT

Please study this function for instance: https://dax.guide/dateadd. It has a passage about what a proper date table is.

Re: Using Measures to calculate a column

Anonymous — Wed, 15 Sep 2021 15:26:54 GMT

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.

Re: Using Measures to calculate a column

KimStahl94 — Thu, 23 Sep 2021 14:59:00 GMT

Hi Daxer,

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
KimStahl94