Make relationship active with more than 1 column

[mitsurya]

In my Fact table I have 2 columns (overdue and completed) and those are connected to dimension table "Status" column is related to Overdue and completed columns.
However I can make it only one active.

for a "Completed" chart I want a connection with "Completed and Status"
for a "Overdue" chart I want a connection with "Overdue and Status"

what is the best way make 2 columns active dynamically?

Fact Table
========
Overdue
Completed

dimension table
===========

Order --- for sorting
Status --- <1 week completed, <2 weeks completed, 1 week overdue, 2weeks overdue
Type --- completed/overdue

I want to show as shown in the pictures.

Thanks,

Surya.

[Data-estDog]

2 options: 
Option 1: use USERELATIONSHIP on the measure that is being referenced by the graph. Filter the graph to either completed or overdue
example:
TotalOverDueBooks = Calculate(COUNTDISTINCT(BookID), USERELATIONSHIP('FactTable'[ Overdue],'DimTable'[Status] ) 

TotalCompletedBooks = Calculate(COUNTDISTINCT(BookID), USERELATIONSHIP('FactTable'[ Completed],'DimTable'[Status] ) 

Click on graph: Add DimTable[Type] to the "Filters on this visual". Set the filter to Type  = complete or overdue appropriate to the graph in question

Opion 2: Split the dimension into two dimensions DimComplete, DimOverdue. You are treating them separately anyway.

If I answered your question, please mark my post as solution, Appreciate your Kudos 

[mitsurya · Accepted solution]

Thank you so much for your time. somehow it did not work for me. I created a one more dim table since I dont have much time on this.

Thanks,

Surya.

[mh2587]

try treatas 

[mh2587]

use userrelationship function in dax with the columns refrence which are used in inactive relationship this will do the job

[mitsurya]

It already have active connection with "week Completed" column. so trying to make it active with "Overdue" col.

I already tried this but no luck.

SortByOverdue = CALCULATE (

    SELECTEDVALUE(StatusOrder_DIM[Status]) ,

    USERELATIONSHIP('OMIE Data'[Weeks Overdue],'StatusOrder_DIM'[Status] )