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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Filter values for inactive relationships

Task is the main model, I duplicated/replicate to create Table (3). I linked both of them to Calendar table as I was preparing planned vs actual charts. Task I get Planned values and from Table (3) I get Actual values and plot them on the Calendar table date axis.

 

But as additional requirement, I need to create individual (plan vs actual) charts by filtering based on Area and other based on Breakdown, and Task name, etc.

 

To relate the tables i created a table for "Area" as common value and when i tried to give relationship between the tables only one relationship remains active.

And for the table which has active relationship the values shows correct when the filter is used.

Tassk.png

I even used Lookup to directly relate the tables but the relationship still remains inactive. The common values between tables is Task ID but that also becomes inactive.

 

Is there a better way to do it? Do i have to keep creating tables for Area, then Breakdown, etc.?

2 ACCEPTED SOLUTIONS
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

If i understand you correctly,Task is a "actual" table, Task(3) is a "plan" table,

First, don't use "both" directions between "Task" and "calendar", "Task(3)" and "calendar", just "single" can filter the table.

 

Second,to filter values in inactive relationship, you could refer to the following threads:

How to filter tables in DAX without using relationships

https://www.mssqltips.com/sqlservertip/5482/how-to-use-the-treatas-function-in-dax/

https://community.powerbi.com/t5/Desktop/Dax-filtering-using-other-table-column-that-s-not-related/td-p/502796

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

 

In addition, based on my experience, i would not suggest you replicating to create Table (3) (Task(3)).

It would make the data size bigger so that the performance of the report may be affected.

 

You could change the data model as below( have a column to define whether it is "actual" or "plan" values),

then create relationships among tables.

Capture8.JPG

 

If you'd like this method but you have difficult making it work in your case,

Please share an example table structure and some details requirements for creating charts.

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

View solution in original post

v-alq-msft
Community Support
Community Support

Hi, @Zahid_shaikh22

Recommend you to use star schema instead of circle schema for the data model, and not to duplicate the table Task(3), which will enlarge the data size. If you need to get Actual value of Task tables, you can create inactive relationship between the table Tasks and Calendar, then you can create measure or column using USERELATIONSHIP function like DAX below.

 

Measure1=CALCULATE(SUM(Tasks[value], USERELATIONSHIP(Tasks[Actual value], Calendar[Date])))

 

Then you may change the Cross filter direction of relationships among the these tables above from Single to Both , which will take these tables treated as a single table. see more about relationship : Create and manage relationships in Power BI Desktop.

 

If you need to get one column of related table, you can use function LOOKUPVALUE , RELATED or FIRSTNONBLANK.

 

If I misunderstand the your thoughts, please inform me of your expected output. I am glad to solve the problem for you.

 

Best Regards,

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

3 REPLIES 3
v-alq-msft
Community Support
Community Support

Hi, @Zahid_shaikh22

Recommend you to use star schema instead of circle schema for the data model, and not to duplicate the table Task(3), which will enlarge the data size. If you need to get Actual value of Task tables, you can create inactive relationship between the table Tasks and Calendar, then you can create measure or column using USERELATIONSHIP function like DAX below.

 

Measure1=CALCULATE(SUM(Tasks[value], USERELATIONSHIP(Tasks[Actual value], Calendar[Date])))

 

Then you may change the Cross filter direction of relationships among the these tables above from Single to Both , which will take these tables treated as a single table. see more about relationship : Create and manage relationships in Power BI Desktop.

 

If you need to get one column of related table, you can use function LOOKUPVALUE , RELATED or FIRSTNONBLANK.

 

If I misunderstand the your thoughts, please inform me of your expected output. I am glad to solve the problem for you.

 

Best Regards,

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

If i understand you correctly,Task is a "actual" table, Task(3) is a "plan" table,

First, don't use "both" directions between "Task" and "calendar", "Task(3)" and "calendar", just "single" can filter the table.

 

Second,to filter values in inactive relationship, you could refer to the following threads:

How to filter tables in DAX without using relationships

https://www.mssqltips.com/sqlservertip/5482/how-to-use-the-treatas-function-in-dax/

https://community.powerbi.com/t5/Desktop/Dax-filtering-using-other-table-column-that-s-not-related/td-p/502796

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

 

In addition, based on my experience, i would not suggest you replicating to create Table (3) (Task(3)).

It would make the data size bigger so that the performance of the report may be affected.

 

You could change the data model as below( have a column to define whether it is "actual" or "plan" values),

then create relationships among tables.

Capture8.JPG

 

If you'd like this method but you have difficult making it work in your case,

Please share an example table structure and some details requirements for creating charts.

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

Anonymous
Not applicable

Thank you for sharing all of the links and solution

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.