Forum Discussion
Generate table multiple columns
Hi titanconsulting ,
I think this is mostly a modelling quetion.
How about creating two relationships from your teams table ("teams dimension") to your results table ("results fact"). Then you could possibly create a measure with the following logic:
DIVIDE (
CALCULATE ( COUNT ( home_team ), home_points > away_points, USERELATIONSHIP ( id, home_team ) ) +
CALCULATE ( COUNT ( away_team ), home_points < away_points, USERELATIONSHIP ( id, away_team ) ),
CALCULATE ( COUNT ( home_team ), USERELATIONSHIP ( id, home_team ) ) +
CALCULATE ( COUNT ( away_team ), USERELATIONSHIP ( id, away_team ) ) )
Note, the syntax might be wrong, but I hope this gives you an idea anyway.
Let me know if this helps and if not feel free to share your pbix file and I can have a look into it.
Thanks,
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Hi titanconsulting ,
I think this is mostly a modelling quetion.
How about creating two relationships from your teams table ("teams dimension") to your results table ("results fact"). Then you could possibly create a measure with the following logic:
DIVIDE (
CALCULATE ( COUNT ( home_team ), home_points > away_points, USERELATIONSHIP ( id, home_team ) ) +
CALCULATE ( COUNT ( away_team ), home_points < away_points, USERELATIONSHIP ( id, away_team ) ),
CALCULATE ( COUNT ( home_team ), USERELATIONSHIP ( id, home_team ) ) +
CALCULATE ( COUNT ( away_team ), USERELATIONSHIP ( id, away_team ) ) )
Note, the syntax might be wrong, but I hope this gives you an idea anyway.
Let me know if this helps and if not feel free to share your pbix file and I can have a look into it.
Thanks,
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Accepting this as solution because it gave me some ideas to go forward with. Don't know if my solution is the most efficient...but it works. With the amount of data I've got, it'll work.
- Duplicated the games table - Labeled one as Home Game and the other as Away Game.
- Merged Team table to Home Game into new table. Same for Away Game.
- Appended the two tables into a single table - Total Games.
It does work - this is my personal analysis so I not overly concerned about performance especially with the amount of data I've got.
Thanks for the help!