Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
adriansuteu
Advocate I
Advocate I

Filter table based on other two tables sliced values without relationships.

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:

 

2019-09-09 12_32_41-Consultants_Report_Delivery_and_Sales - Power BI Desktop.png

 


This is what I have now:

 

 

Relationship based on delivery dateRelationship based on delivery dateRelationship based on sale dateRelationship based on sale dateMatrix, slicers and wrong information shown in the Additional table detailsMatrix, slicers and wrong information shown in the Additional table details

 

 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

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.

SalesAndDelivery.jpg

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] )
)

SalesAndDeliveryVisual.jpg

My sample .pbix is linked below for you to take a look at.

View solution in original post

3 REPLIES 3
jdbuchanan71
Super User
Super User

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.

SalesAndDelivery.jpg

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] )
)

SalesAndDeliveryVisual.jpg

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.

Mariusz
Community Champion
Community Champion

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)

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.