Table Visual using inactive relationship

[msprog]

Hi ,

I am needing some help here .

I have 2 tables : Dimdate and FactInternetSales.  

There is an active relationship between datekey in DImDate and the OrderDateKey in FactInternetSales.

There are a couple of inactive relationship between datekey in Dimate and the DueDateKey in FactInternerSales. also with Shipdatekey. 

The report is really plain .  For the given date range from DimDate, you show all the salesorders whose orderdate fall in that period using a table visual., as below.

Now comes the tricky part,

I have to add another table visual in the same page , but showing all the orders whose DueDate fall in the above period. THe new table should show the exact fields as above but using the inactive DueDate relationship.

I have done some reading and I think this is not possible in PowerBI. , just thought of checking on this, with the community.

Any help, pointers will be greatly appreciated.

Thanks

[v-jingzhang]

Hi @msprog 

This is able to be realized in Power BI. You will need to create measures for other fields in the second table except DueDate like the following measure with USERELATIONSHIP function to change the relationship used.

M_SalesOrderNumber = CALCULATE(MAX(FactInternetSales[SalesOrderNumber]),USERELATIONSHIP(DimDate[DateKey],FactInternetSales[DueDateKey]))

I create a demo PBIX file with some sample data, you may download from here. Kindly let me know if this works.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[msprog]

Hi,
Thanks to you both for your help.

I have tried to incorporate your suggestions and still no luck. Please see attached PBIX here 

FOr the dates between 22 Oct 2013 and 24th OCt 2013, there are 513 rows with the order date between these dates, and 438 rows with due date between the dates.

HOwever, the table visual that's supposed to show the 438 rows, shows only 3?

Please would anyone sugges where i am going wrong.

thanks

[v-jingzhang]

Hi @msprog 

Sorry that I found USERELATIONSHIP() is probably not suitable in this scenario as on the same date there are multiple orders but MAX() will only get the maximum one of them and miss other data. Here is a guidance article on when to create active or inactive model relationships. In this article, there is a similar example to your scenario, and the workaround is to use two Dim tables and create an active relationship between both Dim tables and Fact table.

I create the second DimDate table in your model and use two date slicers to filter the table visuals. One slicer is from the first DimDate table and interact with OrderDate-based visuals. The other slicer is from the second DimDate table and interact with DueDate-based visuals. You could change the interaction behaviors to decide which slicer filters which table. 

Here is the PBIX file, but unfortunately I haven’t found a workaround to use only one date slicer to filter the visuals.

Additionally, there is a similar topic which may bring some new idea.

https://community.powerbi.com/t5/Desktop/Using-inactive-relationship-for-filtering-purposes/td-p/584...

Best Regards,
Community Support Team _ Jing Zhang

[amitchandak]

@msprog , Try a measure like , Remove any active join using crossjoin

measure =
var _max = minx(allselcted('Date'), Date[Date])
return
calculate([measure], filter(Table, Table[Date] <_min))

Refer: how to use userelation and cross filter: https://www.youtube.com/watch?v=e6Y-l_JtCq4x

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...