cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
adriansuteu Regular Visitor
Regular Visitor

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:

 

 

ss-1.pngRelationship based on delivery datess-2.pngRelationship based on sale date2019-09-09 12_20_41-Consultants_Report_Delivery_and_Sales - Power BI Desktop.pngMatrix, slicers and wrong information shown in the Additional table details

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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
Super User
Super User

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

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

 

Super User
Super User

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

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

adriansuteu Regular Visitor
Regular Visitor

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

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.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 66 members 1,232 guests
Please welcome our newest community members: