Relationship!

[colourfullife]

Hi,

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

thanks,

[v-juanli-msft]

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

2. rename columns as below in each table

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

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

[colourfullife]

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

[BhaveshPatel]

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. 

[colourfullife]

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?  

 

[BhaveshPatel]

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/