Don't miss your chance to take the Fabric Data Engineer (DP-700) exam on us!
Learn moreThe FabCon + SQLCon recap series starts April 14th at 8am Pacific. If you’re tracking where AI is going inside Fabric, this first session is a can't miss. Register now
I have got Calendar table (first one below, only first 3 rows as example) created in DAX.
Calendar =
ADDCOLUMNS(
CALENDARAUTO(),
"Year", YEAR ( [Date] ),
"Month", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH( [Date] )
)
and Maturity table (second one). They are related by Date/SampleDate (1-*).
I use a measure AAA to populate dates when there were some test results with associated Code. To this point it's ok.
AAA = IF(SELECTEDVALUE('Calendar'[Date]) = SELECTEDVALUE('Maturity'[SampleDate]), SELECTEDVALUE('Maturity'[Code]),"")
Goal
I have a table below, values are AAA measure as above. How to fill the days when there were no results in a way that it uses the Code from the previous test, until a new test is done? The ideal results shown in the picture below in red.
Attempts
AAA =
VAR SelectedDate = SELECTEDVALUE('Calendar'[Date])
VAR SampleCode =
CALCULATE(
SELECTEDVALUE('Maturity'[Code]),
ALLEXCEPT('Maturity', 'Maturity'[SampleDate], 'Maturity'[Grower], 'Maturity'[Orchard], 'Maturity'[ManagementArea]),
'Maturity'[SampleDate] <= SelectedDate,
'Maturity'[Code] <> BLANK()
)
VAR PreviousSample =
CALCULATE(
SELECTEDVALUE('Maturity'[SampleDate]),
ALLEXCEPT('Maturity', 'Maturity'[Grower], 'Maturity'[Orchard], 'Maturity'[ManagementArea]),
'Maturity'[SampleDate] < SelectedDate,
'Maturity'[Code] <> BLANK()
)
VAR NextSample =
CALCULATE(
SELECTEDVALUE('Maturity'[SampleDate]),
ALLEXCEPT('Maturity', 'Maturity'[Grower], 'Maturity'[Orchard], 'Maturity'[ManagementArea]),
'Maturity'[SampleDate] > SelectedDate,
'Maturity'[Code] <> BLANK()
)
RETURN
IF(
SampleCode <> BLANK(),
SampleCode,
IF(
SelectedDate > PreviousSample && SelectedDate < NextSample,
CALCULATE(
SELECTEDVALUE('Maturity'[Code]),
ALLEXCEPT('Maturity', 'Maturity'[SampleDate], 'Maturity'[Grower], 'Maturity'[Orchard], 'Maturity'[ManagementArea]),
'Maturity'[SampleDate] = PreviousSample,
'Maturity'[Code] <> BLANK()
),
BLANK()
)
)
To report on things that are not there you need to use disconnected tables and/or crossjoins
If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.
A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.
Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.
| User | Count |
|---|---|
| 53 | |
| 40 | |
| 38 | |
| 19 | |
| 18 |
| User | Count |
|---|---|
| 70 | |
| 69 | |
| 34 | |
| 33 | |
| 30 |