Solved: Filter values for inactive relationships

Anonymous · ‎11-29-2019

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.

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

v-juanli-msft · ‎12-02-2019

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.

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 · ‎12-02-2019

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.