Forum Discussion

dramus's avatar
dramus
Icon for Continued Contributor rankContinued Contributor
8 years ago

Take a look at the DAX function NATURALLEFTOUTERJOIN().

 

I created the following table, which I think gives you what you want.

 

All Dates and Vehicles = NATURALLEFTOUTERJOIN('calendar','Vehicle Information')

 

There needs to be a link between the Calendar and Vehicle Information tables, so that the function knows  what to join on.

dramus's avatar
dramus
Icon for Continued Contributor rankContinued Contributor
8 years ago

Hmm....Just added a second Equipment ID and found that it's not working exactly as needed. 

 

I'll keep working on it.

  • dramus's avatar
    dramus
    Icon for Continued Contributor rankContinued Contributor
    8 years ago

    I have it working. I had to jump through a couple of hoops.

     

    First I created a table with just the Equipment ID's in it.

     

    I then used the CROSSJOIN() function to create a cartesian product table of the dates and the equipment.

    All Dates And Equipment = CROSSJOIN('calendar','Equipment')

    I then added a column to the "All Dates and Equipment" table:

    Was Rented = if(isblank(LOOKUPVALUE('Vehicle Information'[Charge Date],'Vehicle Information'[Charge Date],[Date],'Vehicle Information'[Equipment Id],[Equipment])),"No","Yes")

    I think this gives you what you need.

     

     

    PM me with your email address if you would like the PBIX file.