Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a quite simple model with :
1 dimension table :
Id Attribute
1 | Red |
2 | Yellow |
3 | Green |
1 junk dimension table (combining lots of textual fields)
Id Attribute1 Attribute2
1 | Something | Else |
2 | Bla | Bli |
3 | Blo | Blu |
1 fact table
IdFact Value JunkDimFK LoadDate DimFK
1 | 2 | 1 | dimanche, 1 janvier 2023 | 1 |
2 | 3 | 2 | lundi, 2 janvier 2023 | 1 |
3 | 2.6 | 3 | dimanche, 1 janvier 2023 | 2 |
4 | 5 | 1 | lundi, 2 janvier 2023 | 2 |
5 | 0.5 | 1 | dimanche, 1 janvier 2023 | 3 |
6 | 0.1 | 3 | lundi, 2 janvier 2023 | 3 |
1 calendar table
In my real scenario the dataset uses DirectQuery.
Here is a more simple model but it looks quite the same as the one I'm struggling with.
I need to calculate two things :
I'd like to use measures as calculated columns aren't that good in DirectQuery mode.
So far, I managed to get the right latest value from the fact only when I don't use the dates or the values from the fact with aggregation. As soon as I add the date in the table (or the value without aggregation), my measure isn't working.
Latest date value Measure =
VAR _lastDate =
LASTDATE('Fact'[LoadDate])
RETURN
MAXX(
FILTER(
'Fact',
'Fact'[LoadDate] = _lastDate
),
'Fact'[Value]
)
And here is the not desired result for the fact values:
For the textual value coming from the JunkDim, I am quite at the same spot as above except I don't manage to make any working measure as soon as I use the JunkDim value instead of its Fact's foreign key.
Here is the desired result :
Your help will be greatly appreciated.
Thanks in advance.
Solved! Go to Solution.
Try these measures:
Value = MAX ( 'Fact'[Value] )
Latest Value =
VAR _lastDate =
CALCULATE ( MAX ( 'Fact'[LoadDate] ), ALL ( 'Calendar' ) )
VAR _lastDateValue =
CALCULATE ( [Value], ALL ( 'Calendar' ), 'Fact'[LoadDate] = _lastDate )
RETURN
IF ( NOT ISBLANK ( [Value] ), _lastDateValue )
Latest Junk Dimension Attribute1 =
VAR _lastDate =
CALCULATE ( MAX ( 'Fact'[LoadDate] ), ALL ( 'Calendar' ) )
VAR _lastJunkDimFK =
CALCULATE (
MAX ( 'Fact'[JunkDimFK] ),
ALL ( 'Calendar' ),
'Fact'[LoadDate] = _lastDate
)
VAR _lastJunkDimAttribute1 =
MAXX (
FILTER ( ALL ( JunkDim ), JunkDim[Id] = _lastJunkDimFK ),
JunkDim[Attribute1]
)
RETURN
IF ( NOT ISBLANK ( [Value] ), _lastJunkDimAttribute1 )
The date filter context in the visual needs to be removed via ALL (or ALLSELECTED if you have a date slicer) in order to get the latest LoadDate. The NOT ISBLANK expression is necessary to avoid generating a row for each date in Calendar.
Proud to be a Super User!
Try these measures:
Value = MAX ( 'Fact'[Value] )
Latest Value =
VAR _lastDate =
CALCULATE ( MAX ( 'Fact'[LoadDate] ), ALL ( 'Calendar' ) )
VAR _lastDateValue =
CALCULATE ( [Value], ALL ( 'Calendar' ), 'Fact'[LoadDate] = _lastDate )
RETURN
IF ( NOT ISBLANK ( [Value] ), _lastDateValue )
Latest Junk Dimension Attribute1 =
VAR _lastDate =
CALCULATE ( MAX ( 'Fact'[LoadDate] ), ALL ( 'Calendar' ) )
VAR _lastJunkDimFK =
CALCULATE (
MAX ( 'Fact'[JunkDimFK] ),
ALL ( 'Calendar' ),
'Fact'[LoadDate] = _lastDate
)
VAR _lastJunkDimAttribute1 =
MAXX (
FILTER ( ALL ( JunkDim ), JunkDim[Id] = _lastJunkDimFK ),
JunkDim[Attribute1]
)
RETURN
IF ( NOT ISBLANK ( [Value] ), _lastJunkDimAttribute1 )
The date filter context in the visual needs to be removed via ALL (or ALLSELECTED if you have a date slicer) in order to get the latest LoadDate. The NOT ISBLANK expression is necessary to avoid generating a row for each date in Calendar.
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |