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.
Hello.
I have the following situation:
The same persons are salespeople and the deliver certain services.
Table Delivery_All (contains the sales)
Table Delivery_All_D (contains the deliveries)
The same product is sold and delivered. But it can be sold and delivered in the same month/in different months and also it can be sold and delivered by the same person or by two different persons.
Now, the thing is that I need to have a situation with what each of the persons delivered/sold in each month. Because I have two dates (delivery and sales) I have to create two different tables and put the measures in this tables. Because I have the same service both delivered and sold, I have to link the two tables (Delivery_All, Delivery_All_D) with the Courses table.
Now I have created measures and everything else so that I get the right indicators. But what I can not figure out is how to get the Courses filtered based on the context selected by the user. Now it looks like this.
I have a relationship between "Courses[ID]" -> "Delivery_All[ID]" and "Courses[ID]" -> "Delivery_All_D[ID]". I know that I can not use both direction relationships because of the structure that I have. I imagine that I can somehow create a new table that will union the selected (via slicers) ids from Delivery_All and the selected ids from Delivery_All_D and then create a relationship between this new table and the Courses table. That way things will work perfect, but I do not know how to create a table that takes into consideration sliced values (without relationships, of course). I know how to use union for usual scenarios.
I would really appreciate some help as this issue is costing me a lot of time already.
Check out the screen shots:
This is what I want to do:
This is what I have now:
Solved! Go to Solution.
Hello @adriansuteu
I'm thinking you can get what you are looking for using something like the attached. The default relationship between the data and the owners / dates in on sales_date and sales_owner_id but the same table also has the Delivery_Date and Delivery_Owner_ID. You keep just one fact table that has all your detail.
So calculating sales amount by sales owner and sales date is just SUM ( Revenue_Amount ). Then, when we want to look at it from a delivery perspective we can activate the other relationships.
Delivery Revenue Amount = CALCULATE ( SUM ( 'Table'[Revenue_Amount] ), USERELATIONSHIP ( 'Table'[Delivery_Date], Dates[Date] ), USERELATIONSHIP ( 'Table'[Delivery_Owner_ID], Owners[Owner_ID] ) )
My sample .pbix is linked below for you to take a look at.
Hello @adriansuteu
I'm thinking you can get what you are looking for using something like the attached. The default relationship between the data and the owners / dates in on sales_date and sales_owner_id but the same table also has the Delivery_Date and Delivery_Owner_ID. You keep just one fact table that has all your detail.
So calculating sales amount by sales owner and sales date is just SUM ( Revenue_Amount ). Then, when we want to look at it from a delivery perspective we can activate the other relationships.
Delivery Revenue Amount = CALCULATE ( SUM ( 'Table'[Revenue_Amount] ), USERELATIONSHIP ( 'Table'[Delivery_Date], Dates[Date] ), USERELATIONSHIP ( 'Table'[Delivery_Owner_ID], Owners[Owner_ID] ) )
My sample .pbix is linked below for you to take a look at.
Thank you very much for your answer and the time you spend creating the report.
I have started with this kind of structure and I had the inactive relationship for the Owner... but I never thought to make 2 inactive relationships on the same table and use them like that on DAX. I didn't know it is possible. That is why I split the data in 2 separate tables. Thanks a lot. This will be really helpful for the current and future reports.
Hi @adriansuteu
Do the below two tables originally belonged to one table?
Table Delivery_All (contains the sales)
Table Delivery_All_D (contains the deliveries)