Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mitsurya
New Member

Make relationship active with more than 1 column

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.

mitsurya_0-1696939315963.png

 

 

mitsurya_1-1696939343003.png

Thanks,

Surya.

1 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.

View solution in original post

5 REPLIES 5
Data-estDog
Resolver II
Resolver II

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

DataestDog_0-1696949547955.png

 

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 

 

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

try treatas 


Did I answer your question? If so, please mark my post as a solution!


Proud to be a Super User!




LinkedIn Icon
Muhammad Hasnain



mh2587
Super User
Super User

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


Did I answer your question? If so, please mark my post as a solution!


Proud to be a Super User!




LinkedIn Icon
Muhammad Hasnain



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] )  

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors