Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gck02
Frequent Visitor

Display latest value on all rows for each category and textual value

Hello,

 

I have a quite simple model with :

1 dimension table :

Id      Attribute

1Red
2Yellow
3Green

 

 

1 junk dimension table (combining lots of textual fields)

Id        Attribute1    Attribute2

1SomethingElse
2BlaBli
3BloBlu

 

1 fact table

IdFact Value JunkDimFK  LoadDate              DimFK

121dimanche, 1 janvier 20231
232lundi, 2 janvier 20231
32.63dimanche, 1 janvier 20232
451lundi, 2 janvier 20232
50.51dimanche, 1 janvier 20233
60.13lundi, 2 janvier 20233

 

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.

gck02_0-1679488466613.png

 

I need to calculate two things :

  • The latest value in the fact table for each element of the dimension: even when the table displays all the dates, I'd like to show the same value for each row, i.e. the latest value based on the more recent date.
  • The latest textual value coming from my junk dimension

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:

gck02_2-1679489395377.png

 

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 :

gck02_3-1679489691400.png

 

Your help will be greatly appreciated.

 

Thanks in advance.

 

 

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@gck02,

 

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 )

 

DataInsights_0-1679921253958.png

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
DataInsights
Super User
Super User

@gck02,

 

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 )

 

DataInsights_0-1679921253958.png

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@DataInsights,

 

Thanks a lot, it works perfectly. And thanks for the clear explanation!

 

Best regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.