Forum Discussion

dominikRieder's avatar
dominikRieder
Frequent Visitor
6 years ago
Solved

Left-outer-join on "Dax Created" Calendar table

Hello everyone,

 

I am currently struggeling with an issue which seems to be a simple problem, but not simply solvable with Power BI

 

Lets say I have a Table "TicketInformation". The data of this table comes from a SQL-Database and is created with a power Query command.

Every ticket has a "date" field.

 

Then I have a second table called "Calendar" created with the DAX Function:

Kalender = CALENDAR(
DATE( YEAR( TODAY() ); MONTH(TODAY()) - 3; 1);
DATE( YEAR( TODAY() ); MONTH(TODAY()); EOMONTH(MONTH(TODAY()); 0)
))
It creates dates for the last three months
 
The Goal is to show the Amount of time spend for all Tickets per day on a line chart, something like this:

 
Unfortunately the "Null" values are not handled correctly. On weekends e.g there are no entries in the tickettable but still it is not shown as zero in the line chart (instead a straight line which "skips" the days").
 
The Relationship has been set accordingly to a one-to-many between the Calendar table and the Tickettables "Date" column.
(Clicking "show elements with no data" does not change anything)
 
In my second approach I wanted to create a new table with a Left-outer-Join between calendar table and Tickettable. Unfortunately the Calendar Table does not appear as an available Table for merging, when going through "edit queries -> merge queries".
 
What is the best way to create a table that contains every single date from Calendar and all matching dates from Ticket-table or null if not available?
 
Any help is appreciated very much.
 
Best Regards,
Dominik
Need Help
Tutorial Requests
  • sturlaws's avatar
    sturlaws
    6 years ago

    Hi dominikRieder 

    Try a measure like this:

    Amount of time spent =
VAR _timeSpent =
    SUM ( 'Table'[duration] )
RETURN
    IF ( ISBLANK ( _timeSpent ); 0; _timeSpent )

     If in your calendar table is a date without match in your TicketInformation table, it will return 0 instead of blank. Just make sure your calendar table is resonable. E.g. a typical situation is that you have a budget table that runs until the end of the year, so the date table needs to dates until 31.12. Which means for every day from today untill 31.12, this measure will return 0, which might be undesired behaviour. You can work around this by adding a column, e.g. IsPastDates, in you calendar which is 1 when date<=today(), and filter your visual by this IsPastDates = 1.

    Cheers,
    Sturla

    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

     

     

1 Reply

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hi dominikRieder 

    Try a measure like this:

    Amount of time spent =
VAR _timeSpent =
    SUM ( 'Table'[duration] )
RETURN
    IF ( ISBLANK ( _timeSpent ); 0; _timeSpent )

     If in your calendar table is a date without match in your TicketInformation table, it will return 0 instead of blank. Just make sure your calendar table is resonable. E.g. a typical situation is that you have a budget table that runs until the end of the year, so the date table needs to dates until 31.12. Which means for every day from today untill 31.12, this measure will return 0, which might be undesired behaviour. You can work around this by adding a column, e.g. IsPastDates, in you calendar which is 1 when date<=today(), and filter your visual by this IsPastDates = 1.

    Cheers,
    Sturla

    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.