Forum Discussion

adriansuteu's avatar
adriansuteu
Icon for Advocate I rankAdvocate I
7 years ago
Solved

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:

 

 


This is what I have now:

 

 

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

 

 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    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.

3 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    7 years ago

    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.

    • adriansuteu's avatar
      adriansuteu
      Icon for Advocate I rankAdvocate I
      7 years ago

      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's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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