cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ronpadz Regular Visitor
Regular Visitor

create DAX expression that includes outer members of an inner join

My date dimension has a Workday Flag Indicator which I need to use as the basis for the denominator for measures derived from the related fact table. These measures must divide dollars or quantities by the number of work days in the period, whereby that period is dynamic with respect to date-related slicer selections and the current date (i.e. AverageRevenuePerWorkday). The problem is that the date dimension is inner joined to the fact table, and if there isn't a transaction fact on a given workday in the result set, that date won't be counted as workday in the period.

 

So the question is how to formulate a DAX expression that will return the actual number of workdays in the given dynamic period of time from the date dimension that is irrespective to the related facts? Of course any one date-related slicer must act on both the numerator and denominator logic so I can't use a separate alias of the date dimension for thedenominator.

 

Here is the logic that would return the intended denominator value IF there were fact transactions for each Workday:

Days In Period = CALCULATE(
COUNT('Order Date'[DateKey]),
FILTER('Order Date','Order Date'[Work Day Flag] = TRUE())
)

I'm certain there's a way to do this by applying context but my attempts have been unsuccessful so I would appreciate help from the community.

Thanks

11 REPLIES 11
Super User
Super User

Re: create DAX expression that includes outer members of an inner join

The trick to solve this problem is to remove the relationship.  You can read my article here 

https://www.powerpivotpro.com/2016/12/how-many-working-days-has-an-employee-been-off-work/

 

This article is similar, not identical, but I think it will help you solve the problem.  Jump to the bottom where I show the 2 tables without a relationship.

 

If you need the relationship for other purposes, then make it inactive and then call it using USERELATIONSHIP in your other measures.  

 

 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
ronpadz Regular Visitor
Regular Visitor

Re: create DAX expression that includes outer members of an inner join

Thank you so much for your quick response, Matt. Your article is excellent and I really like the approach you take, however I think this would be vulnerable to error when applied to my problem and here's why: My report uses a Fiscal Month (in the form of YYYY-MM) slicer from an attribute of my date dimension. That date dim also includes columns for FiscalMonthBeginDate and FiscalMonthEndDate that I could key off of to provide the logic:

Days In Period = 
 CALCULATE(
  COUNT('Order Date'[DateKey]),
  FILTER(
   'Order Date','Order Date'[Date] >= MIN(WorkDaysTable[FiscalMonthBeginDate])
    && 'Order Date','Order Date'[Date] <= MAX(WorkDaysTable[FiscalMonthEndDate])
   )
 )

 

 

So far so good. I would need to further refine this to account for accurate calculations in the midst of the current fiscal month as thus:

Days In Period = 
 CALCULATE(
  COUNT('Order Date'[DateKey]),
  FILTER(
   'Order Date','Order Date'[Date] >= MIN(WorkDaysTable[FiscalMonthBeginDate])
    && 'Order Date','Order Date'[Date] <= MAX(WorkDaysTable[FiscalMonthEndDate])
    && 'Order Date','Order Date'[Date] < TODAY() ) --would need to modify to return date only
   )
 )

 

This should work under most scenarios, but unfortunately this is still dependent on the presence of fact records in each Fiscal Month selected in the slicer. There are scenarios whereby other slicers may greatly reduce the number of fact records being referenced, likewise in the early days of a new fiscal month there may not yet be transactions that meet the slicer criteria. In these cases the min or max will not include the intended time period for the count of work days.

 

It would be nice if in a DAX expression there were a means to reference the actual selections made in a slicer. This can be done in tools such as Business Objects. Is there a way to do this in Power BI?

 

Any other ideas?

Super User
Super User

Re: create DAX expression that includes outer members of an inner join

Sorry, I don't understand your explanation of why it won't work. If you remove the relationship and use filter to filter the fact table between the first date in the filter context and the 

last date in the filter context, why won't it work. 

 

Eg

 

if the slicer selects Jan through March and there are no records for Feb, why is this a problem?  As I understand your requirement, the fact that there are no records in Feb is irrelevant. 

 

Of course my understanding of the problem may be flawed.  



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
ronpadz Regular Visitor
Regular Visitor

Re: create DAX expression that includes outer members of an inner join

Let's say that in the period slicer, Jan and Feb are selected and today is Feb 3 of 2017. Now let's say that there are not yet facts in Feb that meet all the slicers' criteria.  We still need to include Feb 1 & 2 as work days in the calculation denominator, but these dates will fall off because the MAX(WorkDaysTable[FiscalMonthEndDate]) for the available data will be January 31.

 

I hope I've explained this condition adequately.

MarkS Member
Member

Re: create DAX expression that includes outer members of an inner join

HI Ron,

Do you have an actual Calendar table that has the workday flag that is related to the Orders table, or did you merge them?

With an actual Calendar table the formula would be

Days In Period=CALCULATE(COUNT('Calendar'[Date]).

                          FILTER('Calendar'[Work Day Flag]=True()

                          && 'Calendar'[Date]<='Order Date'[Date Key]))  -- Cound also use the TODAY() function here.

 

Where you are counting the workdays from the Calendar table not your orders table therefore it will not matter if there were no orders on some day(s).

 

 

 

 

ronpadz Regular Visitor
Regular Visitor

Re: create DAX expression that includes outer members of an inner join

Using && 'Calendar'[Date]<='Order Date'[Date Key] defines the Calendar date range based on the order date so that order date is still subject to a lack of data in its join to the fact table. Consider the following simplified illustration. If the slicer on the Order_Date_Dim includes Jan and Feb, it will only return the Jan data data because no Feb data exists in the fact table yet.

 

 

Using && 'Calendar'[Date]<=TODAY() function would overcome this, however the user may not want to include the current time period (i.e. month) in the slicer selection

ronpadz Regular Visitor
Regular Visitor

Re: create DAX expression that includes outer members of an inner join

This post was eliminated because it was inadvertnetly posted before being completed.

MarkS Member
Member

Re: create DAX expression that includes outer members of an inner join

Sorry,

there is a mistake in the previous filter && 'Calendar'[Date]<='Order Date'[Date Key]  should have been

&& 'Calendar'[Date]<=MAX('Order Date'[Date Key])

 

If you use the TODAY function and use a filter to exclude the current month then only the workdays from the previous full months will be included in resulting count.

ronpadz Regular Visitor
Regular Visitor

Re: create DAX expression that includes outer members of an inner join

I really appreciate your patience in working through this with me.

 

Using && 'Calendar'[Date]<='Order Date'[Date Key] defines the Calendar date range based on the order date so that order date is still subject to a lack of data in its join to the fact table.

 

Consider the following simplified illustration. Assume the slicer on the Order_Date_Dim has January and February calendar months selected and today is Feb 3. We would want our denominator measure to include the 3 work days in February in addition to the January work days. But because no Feb data exists in the fact table yet, only the Jan data rows from the Order Date Dim will be returned. Therefore when you perform "'Calendar'[Date]<=MAX('Order Date'[Date Key])" it will not pick up our Feb days because the MAX in the returned data set is 1/31. Make sense?

Untitled1.png

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 263 members 3,054 guests
Please welcome our newest community members: