Data modelling and transformation

[Anonymous]

Hi Community,

I am bit new to power BI and juggling with an issue.

I have two excel table

Table A:

Name	Date	hours planned
Grey	1/2/2020	4
Matt	4/2/2020	6
Kevin	1/5/2019	2
Sarah	26/3/2020	8

Table B: It has similar name from table A but not all names. I made Name as my primary key

Name	Date	Hours billed
Sarah	26/3/2020	3
Grey	1/2/2020	9

How do I manage relationships between table or build a new measure to make a visualization for planned vs billed hours, planned hours, calender quarter results etc?

Hoping to get a direction from you all.

Thanks

[Anonymous]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Anonymous · Accepted solution]

Hi @Anonymous 

You may relate two tables by Name columns. If your relationship is one to one you can build a table visual and add Hours billed to do planned vs billed, it works well.

However if your relationship is many to many(there are more than one same names in Name columns, maybe they have different Date value), you may build a measure to compare Hour planned and Hour billed.

Measure:

Hours billed = CALCULATE(SUM('Table B'[Hours billed]),FILTER(ALL('Table B'),'Table B'[Date]=MAX('Table A'[Date])&&'Table B'[Name]=MAX('Table A'[Name])))

For more info to relationships and dax you may refer to blogs as below.

Create and manage relationships in Power BI Desktop

Calculated Columns and Measures in DAX

You can download the pbix file from this link: Data modelling and transformation

Best Regards,

Rico Zhou

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

[rubinboer]

Hi there

Power BI will detect the relationship but you will have to make sure all the datatypes are correct, especially dates. once that is done review your relationship and create measures for the planned vs billed. the others will be available by default.

hope it helps regards