Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have one fact table (first image) and a dimension time including duration per shift (dag=day and avond=evening).
For each line in the fact table (first image) I want a calculated column that calculates whether the shift is day or evening (based on the shifts in the second image).
All help is greatly appreciated
Solved! Go to Solution.
Sorry, it's missing the VALUES( )
WhatShift = CALCULATE ( VALUES(DimDate[Shift]), FILTER ( DimDate, DimDate[Datum] = FactTable[DatumTijd].[Date] && FactTable[DatumTijd] >= DimDate[StartTijd] && ( FactTable[DatumTijd] <= DimDate[EindTijd] || DimDate[EindTijd] = BLANK () ) ) )
Try this for your calculated column:
WhatShift = CALCULATE ( DimDate[Shift], FILTER ( DimDate, DimDate[Datum] = FactTable[DatumTijd].[Date] && FactTable[DatumTijd] >= DimDate[StartTijd] && ( FactTable[DatumTijd] <= DimDate[EindTijd] || DimDate[EindTijd] = BLANK () ) ) )
I see that there's a blank for one of the [EindTijd] values. The code assumes that's equivalent to the end of the day.
Hi @AlB,
After calculate it's impossible to put DimDate[Shift], any idea how to resolve? Proper connections have been made in the data model so that's not hte issue
Sorry, it's missing the VALUES( )
WhatShift = CALCULATE ( VALUES(DimDate[Shift]), FILTER ( DimDate, DimDate[Datum] = FactTable[DatumTijd].[Date] && FactTable[DatumTijd] >= DimDate[StartTijd] && ( FactTable[DatumTijd] <= DimDate[EindTijd] || DimDate[EindTijd] = BLANK () ) ) )
Hi @Luukvv93
You mean you want to take the Date/Time value in your first table in column 'DatumTijd' and check where that value falls in the second table, i.e. between what StartTijd and EindTijd and from there determine whether it is Avond or Dag?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |