Forum Discussion
Data modelling and transformation
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
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.
3 Replies
rubinboerResolver II
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
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.
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