Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I need to be able to say the outcome recorded for the max date within the reporting period. The max date sits on 1 table and the outcome on another table.
I have the max date:
var _enddatecontext=MAX('transform DimCalendar'[ActualDate])
Return
CALCULATE(
MAX('dl FactCATServiceReview'[CATServiceReviewReviewDate])
,FILTER('dl FactCATServiceReview',
('dl FactCATServiceReview'[CATServiceReviewReviewDate]<=_enddatecontext)))
But then I need to say look at the related table and return the status text.
Linking column [AccommodationStatusID]
Table: dl_DimCATReviewAccommodationStatus
Column: [CATReviewAccommodationStatusText]
Solved! Go to Solution.
SOLVED.
I merged the tables to exclude the related table look-up problem.
Then turned the calculation into a variable.
DRAFT Accomodation End =
var _enddatecontext=MAX('transform DimCalendar'[ActualDate])
var _MaxDate=CALCULATE(MAX('dl FactCATServiceReview'[CATServiceReviewReviewDate]),FILTER('dl FactCATServiceReview','dl FactCATServiceReview'[CATServiceReviewReviewDate]<=_enddatecontext))
var _Result=
CALCULATE(MAX('dl FactCATServiceReview'[AccommodationSituation]),'dl FactCATServiceReview'[CATServiceReviewReviewDate]=_MaxDate)
RETURN
_Result
SOLVED.
I merged the tables to exclude the related table look-up problem.
Then turned the calculation into a variable.
DRAFT Accomodation End =
var _enddatecontext=MAX('transform DimCalendar'[ActualDate])
var _MaxDate=CALCULATE(MAX('dl FactCATServiceReview'[CATServiceReviewReviewDate]),FILTER('dl FactCATServiceReview','dl FactCATServiceReview'[CATServiceReviewReviewDate]<=_enddatecontext))
var _Result=
CALCULATE(MAX('dl FactCATServiceReview'[AccommodationSituation]),'dl FactCATServiceReview'[CATServiceReviewReviewDate]=_MaxDate)
RETURN
_Result
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |