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
IanCockcroft
Post Patron
Post Patron

Indicate if record has not come through yet

Hi guys, I have a bit of a condundrum which I am struggling with.

I have a SQL table that gets updated with various system status data.

if a system runs and finishes with a success or failure, a record is written to the table alng with the  date.

Depending on if the process was a success or failure, an icon is displaid. this works fine.

 

The problem is, when a date is selected where no record has come through yet, a late icon to be displayed.

something like this

date statusURL_ID
2020-07-01Success1
2020-07-02Success1
2020-07-03Success1
2020-07-04fail2
2020-07-05Success1
2020-07-06Success1
2020-07-07Success1
2020-07-08fail2
2020-07-09Success1
2020-07-10Success1
2020-07-11fail2
2020-07-12Success1
2020-07-13Success1
2020-07-14Fail2
  3

 

If the 15th is selected in a filter, no record exists, so a URL_ID of 3 needs to be assigned and the reevant icon show.

 

Any ides guys ?

thanks

Ian

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Use the date from your dates/calendar table as the basis of your visual, not the event date.  Then you can do calculated columns or measures as needed to compute the status for each dates[date], via crossfilter(,,none) if you can create inactive relationships or other table math if not.

 

Here's an exampe of a calculated column in the dates table with relationship between the date fields.

 

Annotation 2020-07-13 174424.png

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

Use the date from your dates/calendar table as the basis of your visual, not the event date.  Then you can do calculated columns or measures as needed to compute the status for each dates[date], via crossfilter(,,none) if you can create inactive relationships or other table math if not.

 

Here's an exampe of a calculated column in the dates table with relationship between the date fields.

 

Annotation 2020-07-13 174424.png

aaaaaaahhhhhhhh, of course, the date dimension

thanks so much lbendlin 

I'm sure I can make it work

ian

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.