Forum Discussion

adriansuteu's avatar
adriansuteu
Advocate I
6 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 deliveri...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 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.

jdbuchanan71's avatar
jdbuchanan71
Super User
6 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
    Advocate I
    6 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.