Max no of working days by team

[Anonymous]

Hi everyone,

I am trying to create a report which shows the maximum amount of working days it has taken someone to submit a report, split by team.  

This is what the result should look like:-

Team Name	Max no of Reporting Days
North Team	3,100
South Team	3,000
Central Team	512

I have created working days as a calculated column in the table Jobs, which does seem correct 

Jobs[Reporting Days]

Reporting Days =

CALCULATE(

COUNTROWS(DimDate),

DATESBETWEEN(DimDate[Date],

Jobs[Completed Date],

Today()-1

),

DimDate[Is Working Day Flag] =1,

ALL (DimDate[Date])

)

I can't seem to create the DAX for a measure to show this.  This is what I have so far:-

Measure 2 =

CALCULATE(

MAX(Jobs[Reporting Days]),

ALLEXCEPT(Users,Users[Team Name])

)

And this is what it is showing, the overall maximum number against every team.

 I've attached the relationships between the tables so I'm wondering whether this is where the issue is?

(Jobs is joined to DimDate off screen on Completed Date)

Can anyone help?

Thank you muchly

[Anonymous]

Ah, okies.  Thank you for this!

I have updated the relationship between jobs and LastAppointments to bidirectional and it does return the table and split as I wanted.  The LastApppointments table is the last appt on a job so thinking about it, this should really be a one-to-one relationship.

I know this is advised against but I don't understand DAX well enough to know why and whether this will have a negative effect later.  The alternative on the Microsoft site said to put a filter on the visualisation, and this didn't have the desired effect. 

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-bidirectional-filtering 

[Whitewater100 · Accepted solution]

Hi:

Your calculation looks right under normal circumstances. On the model,it's hard to say the exact problem but I noticed a few things.

Typically your Date Table should only be on the one side of the relaltionship with your Fact Tables.

There looks like you have directly connectd fact tables, which you do not want to do. Fact Tables have the statistics and have the many * sign next to them.  Right now your users table can not filter jobs unless you make the relationship between LastAppointments and Jobs bi-directional, however those relationships can be tricky and mess up other results. (also it's connecting fact tables). If Jobs connected directly to users (many to one) your calc could also work. I'll paste a simple model design below. Rob Collie suggests the one sided tables on top and fact(many) on the bottom because your top tables (one side) filter your fact tables many side. I hope this helps!