cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IanCockcroft
Helper V
Helper V

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
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors