DAX Fridays! #183: How to deal with multiple dates in Power BI

So you have order date, shipped date, required date....and only one calendar. How do you manage that in power bi and DAX?

This scenario is often called role playing dimensions and in this video I will show you two ways to solve it. :)
Happy Friday!!

Links to videos mentioned:
Introduction to data modeling:

Understanding relationships:

Userelationship function in DAX:

Review of factfulness:


SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:


Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲


************





************


QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Twitter ► @curbalen, @ruthpozuelo

#CURBAL #SUBSCRIBE

[carloszamudioysla]

This video just came on time for me as I needed to play in a report with creation date vs resolution date!!! Thanks a lot Ruth!

[ursusmaritimus]

Thank you for the video, where you definitely need two calendar tables is e.g. if you need to have a matrix with order date in rows and shipping date in columns.

[dannyhometown]

Happy Friday Ruth, relationship is a part of important fundation of PBI. Thanks for sharing. Have a nice weekend. - Danny

[madiakns]

This is is very helpful! Happy weekend Ruth...

[emmanuelrodriguez]

One situation i am dealing with now is taking shipper and consignee locations amd linking them to a location ID Dim table with which I will be using to create a pivot view that takes both fields as row context to create a from>to lane. I guess that means more duplicate tables for me!

[martinbubenheimer]

I also try to avoid role playing tables and prefer role playing relationships. You need a second table if you want to put slicers for two roles on one report page. E.g. filter all sales opportunities by created in March 2020 and completed in June 2020. BUT: This does not need to be a role playing table. It can just be values for a slicer like the ones generated for what-if simulations and then you use functions like SELECTEDVALUE or TREATAS to apply the slicer selection.

[didierterrien]

Thank you for the video Ruth,
I suppose it is necessary to have multiple date tables if we want to use different dates in slicers. Or maybe you know a trick?

[luisjimenez]

Thanks a Lot!! You`re fantastic!! Congratulations!!

[pk]

Hi Ruth, can you please post a link to your other channel where you have reviewed Factfulness? Thanks!

[SkipMiLuu]

Hi is there not maybe a better way to this by adjusting the model a bit? Could you perhaps have a dimension table for shipping date and order date -n like you mentioned, but then have one "bridging" date table that connects the shipping date and order date tabe to the facts table in the model?

I suppose this would end up being a snowflake model?

[pumpkinshoot]

Thank you Ruth, as always great videos! Im creating creating date vs ending date - Everytime I create a relationship between my fact table and date table my dates in fact tables breaks. Havent found a solution for that :( Do you have any idea? Thank you!

[micgiggs]

Hi Ruth,

I am new to Power BI and I hope to do a support dashboard on whether a robot job ended according to the scheduled time slot. A job might be scheduled to run more than once (such as job ABC scheduled to run in 2 timeslot).

As there are several entries in the job_compliance table for the same job, my intention is to get the latest job "ended" record for the current date and check compliancy. For eg, when I am running the report for today (16 Mar), job ABC has 2 "jobs" in the job_compliancy table. I only want the latest "ended" job (end_time=14/3/2021 7:02:00 PM, key=1234567). Then check the compliancy. The jobs MNO, PQR, and STU will not be counted / displayed in today's dashboard.


Scheduled Job Timings:
Job|start_time|end_time
ABC|12:00 AM|1:00 AM
EDF|1:00 AM|2:00 AM
GHI|2:00 AM|3:00 AM
JKL|3:00 AM|4:00 AM
MNO|4:00 AM|5:00 AM
PQR|5:00 AM|6:00 AM
STU|6:00 AM|7:00 AM
VW|7:00 AM|8:00 AM
XYZ|8:00 AM|9:00 AM
ABC|9:00 AM|10:00 AM
JKL|10:00 AM|11:00 AM
PQR|11:00 AM|12:00 PM



compliance table:

ABC|3/16/2021 19:02|ABC execution ended|1234567|N
EDF|3/16/2021 1:30|EDF execution ended|89101112|Y
GHI|3/16/2021 3:59|GHI execution ended|10111213|
ABC|3/16/2021 19:00|ABC execution started|1234567|
EDF|16/03/2021 1:25|EDF execution ended|30405060|
GHI|16/03/2021 3:55|GHI execution ended|98765432|
JKL|3/16/2021 4:38|JKL execution
MNO|3/16/2021 4:02|MNO execution
PQR|3/13/2021 3:25|PQR execution
STU|3/12/2021 6:45|STU execution
VW|3/16/2021 14:52|VW execution
XYZ|3/16/2021 16:19|XYZ execution
JKL|3/16/2021 4:30|JKL execution
ABC|16/03/2021 18:02|ABC execution ended|2234567|
ABC|16/03/2021 18:00|ABC execution started|2234567|
EDF|16/03/2021 1:20|EDF execution started|30405060|
GHI|16/03/2021 3:50|GHI execution started|98765432|
XYZ|16/03/2021 16:19|XYZ execution


how do I populate the Is_Compliance value (Y or N)? Would really appreciate some guidance. tx.

[venkat]

How to create multiple slicers in a single line horizontally like YTD WTD PWK PTD TTM so when we click on YTD filter report will display according to that..Please let me know if you have done this

[bradj]

I remember creating copies of dimension tables before I knew about role playing / USERELATIONSHIP. It was a sloppy model! Lol

[ITSNev]

Great Video, but how can I created a Matrix table, say with Order Dates on the Rows, and Invoice Dates in the Columns, so end up with

Q1 Q2 Q3 Q4
Q1 n n n n
Q2 n n n n

So this gives me the Revenue against the Invoice Value, based on all Order in Q1.

[hokki]

Why not make all relationships to the calendar inactive, this would make the model and all measures symmetric. No relationship would be preferred.