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
gavinf4444
Frequent Visitor

Creating a table with 2 stages of filters

Hi there, I'm trying to create a measure so I can effectively show the latest "Comment" in the table for the latest date however I don't want to show anything for that "Site" if the "Comment" is "N/A".

 

SiteCommentDate
AAll OK01/01/2023
ARan Late02/01/2023
AN/A03/01/2023
AOK04/01/2023
AOK05/01/2023
AN/A06/01/2023
BN/A01/01/2023
BRan Late02/01/2023
BN/A03/01/2023
BOK04/01/2023
BAll OK05/01/2023
BLate06/01/2023

My code so far creates a table to filter out any "N/A" values -

 

Comments = 
var _CommentsTable = 
SELECTCOLUMNS (
    CALCULATETABLE (
        factCommentsLog,
        FILTER (
            factCommentsLog,
            factCommentsLog[Comments] <> "N/A"
        )
    ),
    "Plant", factCommentsLog[Site],
    "Issues", factCommentsLog[Comments],
    "Date", factCommentsLog[Date],
    "Last Date",MAX(factCommentsLog[Date])
)

RETURN
CONCATENATEX(_CommentsTable,[Last Date]& [Date]& " - " & [Site]&" - "&[Comments],UNICHAR(10))

 

This code seems to work OK in filtering out any Comments values which are N/A.  In my next step I want to filter this new table "_CommentsTable" for the latest date so that I would expect it to return just the row for Site B, "Late", 06/01/2023 as Site A is N/A on this date.

I've tried creating a measure for MAX(factCommentsLog[Date]) and can see that this returns "06/01/2023" however if I then try and filter the _CommentsTable by that it doesn't do it.  If I try to filter the _CommentsTable by using [Date] = DATE(2023,1,6) then it works fine so I'm at a loss as to why this is happening.

1 REPLY 1

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.

Top Solution Authors