Forum Discussion
Post PatronCalculating MIN(Date) with data from three different tables...
I have three tables: Fact_Keys, Dim_Details, Dim_Dates
Fact_Keys has key columns to the two dimension tables: SK_Details and SK_Date.
Dim_Details has columns SK_Details, ServiceID, ServiceStatus, BackupDate.
Dim_Date has columns SK_Date, Date.
I need to find out the MIN(Date) for each [ServiceID], and if that date = 1 january 2015, then it takes [BackupDate] instead. The result will be called StartDate.
If this was a flat table I'd just do something like this:
StartDate =
var currentStartDate =
FORMAT(
CALCULATE(
MIN('Fact'[Date]);
ALL('Fact');
'Fact'[ServiceId] = EARLIER('Fact'[ServiceId])
);"dd-mm-yyyy")
RETURN
IF(
currentStartDate = "01-01-2015";
FORMAT('Fact'[BackupDate];"dd-mm-yyyy");
currentStartDate
)
I need to isolate that date in order to count how many services were started in a given time frame (and will also need the stop date, but that's just MAX instead of MIN...)
Any ideas?
7 Replies
- vanessafvg
Community Champion
grggmrtn is it possible to provide some dummy data(n text form)? also how are the relationships set up between these tables
- grggmrtn
Hey vanessafvg I'll try... The actual tables are huge so I'm not even attempting to do a 1:1 dummy, and I might be breaking the logic by trying to replicate this (!)
Logic is, that each ServiceID should have a list of consecutive dates. It's the first and the last of these that I need to isolate.
The two dimensions are related to the fact table through their respective key columns, 1 to many.
Dim_Details
SK_Details ServiceID ServiceStatus BackupDate 1 9 Doesn't matter 5. januar 2016 2 9 Doesn't matter 5. januar 2016 3 20 Doesn't matter 6. januar 2016 4 20 Doesn't matter 6. januar 2016 5 20 Doesn't matter 23. juni 2016 6 27 Doesn't matter 22. februar 2016 7 27 Doesn't matter 16. juni 2016 8 27 Doesn't matter 22. februar 2016 9 27 Doesn't matter 22. februar 2016 Dim_Date
SK_Date Date 1 1. januar 2000 2 31. januar 2000 3 29. februar 2000 4 31. marts 2000 5 30. april 2000 6 31. maj 2000 7 30. juni 2000 8 31. juli 2000 9 21. august 2000 And The fact table just has all values from SK_Details and SK_Date
Hope this makes sense??
- amitchandak
Super User
You should able to create a new column in service table and can get data from other tables like the example given below
New column in Table 1 = maxx(filter(table2,table1[customer] = table2[customer] && table2[option]="construction",table2[value]) New column in Table 1 = maxx(filter(table2,table1[Attribute] = table2[name] && table1[project] = table2[project],table[name])Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin- grggmrtn
I'm sorry amitchandak but I can't quite figure out what you're trying to tell me. The names you use in your example aren't the ones I gave in my description, and you refer to two tables while I spoke of three?
- amitchandak
Try a new column in Dim_Details
StartDate = if( minx(filter(Fact_Keys, Fact_Keys[SK_Details]=Dim_Details[SK_Details]),Fact_Keys['SK_Date']) <date(2015,01,01) ,Dim_Details [BackupDate],minx(filter(Fact_Keys, Fact_Keys[SK_Details]=Dim_Details[SK_Details]),Fact_Keys['SK_Date']) )
