cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Relationship!

 

Hi,

Is there any way of let me connect Sheet1 date to dp list Start plan/ actual , finish plan/ actual?

thanks,

 

relationship.PNG

5 REPLIES 5
Super User
Super User

Re: Relationship!

Hi @colourfullife

 

You can use inactive relationships to create relationships between more than one columns of the dates.

 

You can use USERELATIONSHIP function in your aggregation calculations to refer to those relationships or you can call it on demand activation of relationships.

 

https://powerbi.tips/2017/07/use-multiple-connections-between-tables/

 

You can also use multiple date tables as well but it is bit messy approach and I do not prefer it for the cleaniness of the data model. 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Re: Relationship!

Hi,

 

if the relationship is not active.

things in my report don't work properly..?

 

i've made relationship like below.

but still the table doesn't work properly.

In the table, I took module from DP list. and the rest from start/ finish plan and actual.

the figures are not correct..?

 

i feel like if the relationships become active, it'd be all ok.

or i need to make some measure or table?  

Relationship question.PNGtable1.PNGtable2.PNG 

Super User
Super User

Re: Relationship!

Hi @colourfullife 

 

I would consider this as poor data model design.

 

Your data model is full of bi directional relationships. You can use Power Query to denormalize your tables to achieve the desired output. Relying too much on Relationships and DAX is the worst design practice and should be avoided. 

 

As I mentioned earlier, You can use USERELATIONSHIP function to convert the inactive relationships to active relationships. 

 

Please check out the sqlbi blog article on this topic:

https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Community Support Team
Community Support Team

Re: Relationship!

Hi @colourfullife 

BhaveshPatel points to the right direction.

I make a simple example how to use Power Query to denormalize your tables to achieve the desired output as BhaveshPatel suggested.

1.add a custom column as below in each table

1.png

 

2. rename columns as below in each table

2.png

 

3. append four tables as a new table (note : columns'names should be the same in each table)

3.png

4.close&&apply, then you could create calculated columns or measures in Data model/Report view with DAX.

 

Besides, if using this transform still doesn't work, please show me the structure of "dp list" table, i need to know how "module" relates with the "dates" of "start/ finish plan and actual".

 

Best Regards

Maggie

Highlighted

Re: Relationship!

Hi Thanks for your reply,

 

What if i made a table of measure NOT queries so can't append them.? 

It's the link = PBI file