Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Trying to figure out how to model a dataset with several date conditions in the data.
I have -
Create Date
Business Date
Cancel Date
On Sales Count and Sales Revenue.
Normally I would just create a calendar table to join on one date, but not sure how to configure this. The business wants to see how many sales created, i.e. reserved, and how many canceled, to get to the net on Business date and be able to forecast and budget.
I have these in a filter, but they are not playing well together and I'm not sure what the metrics should be built off of for PY. If I use a Calendar Dimension table, it can only join to one of them.
Solved! Go to Solution.
Hey @Tihannah
You will need to choose one date to be the mandatory in relationship (maybe create or business date). The others dates, you will create a relationship too, but they will be inactived. After that you need write your DAX measure using the function USERELATIONSHIP (USERELATIONSHIP function (DAX) - DAX | Microsoft Learn)
If this posts helps, please mark as solved to help other user find quickly in ou community!
Regards,
Marcel
Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI
Hey @Tihannah
You will need to choose one date to be the mandatory in relationship (maybe create or business date). The others dates, you will create a relationship too, but they will be inactived. After that you need write your DAX measure using the function USERELATIONSHIP (USERELATIONSHIP function (DAX) - DAX | Microsoft Learn)
If this posts helps, please mark as solved to help other user find quickly in ou community!
Regards,
Marcel
Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI
Okay, so I still use a Calender dimension table and create the relation with say "business date". I get using the USERELATIONSHIP function, but how would I create a relationship with the others?
You can create in the same way that you create the relation with business date. Just drag and drop the dates. They relationships will create and they will be unactived automatically and then you can use the DAX function.
Regards,
Marcel
Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI
Currently, the Create Date and Business Dates are set up as dimentsion tables also. Do I create the relationship to those or to the actual fact table that contains them. I always thought I could only create one relation to the tables?
You should have just one dimension calendar table and related this one with you fact table.
Power BI just allow one active relationship. You can create more then one, but these will be deactived to use in DAX measures. 😃
Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI
Gotcha! Totally worked! Thank you!!!!
Nice @Tihannah 😃
Please consider mark as solved to help other users find quickly in our community!
Regards
Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
98 | |
89 | |
74 | |
67 | |
62 |