Generating a sports schedule

[cjd72consulting]

Background - lots of programming experience - but new to Power BI. Playing around with some sports data just to get my feet wet.

I'm assuming this should be easy, but I've banged my head against it for a couple of days.

I have two tables:

Teams

-----

TeamID

TeamName

Games

--------

GameID

GameDate

HomeTeamID

AwayTeamID

How would you go about creating a visualization that represents a schedule in the form of:

GateDate HomeTeamName  AwayTeamName

I don't need every detail explained, just the major steps.  

[CNENFRNL · Accepted solution]

Yes, indeed, like all rookies to DAX, you do miss something simple. I wish you were able to understand this simple solution.

[CNENFRNL]

Simple enough

1. Unpivot HomeTeamID and AwayTeamID
2. Create relationship

[cjd72consulting]

---

@CNENFRNL wrote:

Simple enough

1. Unpivot HomeTeamID and AwayTeamID
2. Create relationship

---

That's what I thought the answer was.  But I still can't get from there to a visualization in the form of a table with the columns:

   GameDate HomeTeamName AwayTeamName

Can you help me get from the unpivoted table to that visualization?  I know I'm probably missing something simple,  but I keep getting stuck.

[CNENFRNL · Accepted solution]

Yes, indeed, like all rookies to DAX, you do miss something simple. I wish you were able to understand this simple solution.

[Dangar332 · Accepted solution]

thats many way but i get solution from below method

  EVALUATE 
     SUMMARIZECOLUMNS('games'[game date],

                      "HomeTeamname",CALCULATE(VALUES(team[teamname]),

             TREATAS(VALUES(games[hometeam id]),team[team id])

                  ),

                  "awayTeamname", CALCULATE(VALUES(team[teamname]),

            TREATAS(VALUES(games[awayteam id]),team[team id])

            )

          )  

             

we need to use Treats to Change Data lineage of game's table  to Team Table

which create Virtual Relationship and through it we able to filter Team table

If you Want to know more about it please visit Here .

 

[cjd72consulting]

Can I ask a followup question?  I've played around with the solution you provided, and I'm still struggling to understand how the CALCULATE FUNCTION works in this expression.

Specifically I'm unclear on where the context comes from such that this measure returns the correct team.  Does the table visual create a context for each row that the measure runs inside of?

[CNENFRNL]

Em.... impossible to explain it in several sentences. CALCULATE() is the grail of DAX. Crack down on it, then you (almost) master DAX. Your DAX journey starts from CALCULATE() and the ultimate goal is also CALCULATE().

CALCULATE – DAX Guide

Tricky thing is that CALCULATE() functions like a 10-layer wrapper; and above all, the most annoying thing is that, up till now, there's no handy debug tool allowing DAX user to debug step-by-step. Tons of abstract details like context transition, filters modification, filtering propagation etc. happen in a sandbox.

Enjoy DAX!

[Sahir_Maharaj]

Hello @cjd72consulting,

1. Create a relationship between the Teams table and the Games table based on the TeamID.

2. Create a new measure in the Games table that concatenates the HomeTeamName and AwayTeamName columns with a hyphen (-) separator.

Matchup = CONCATENATE(Games[HomeTeamName], " - ", Games[AwayTeamName])

3. Create a new table visual and add the following columns in this order:

• GameDate column from the Games table
• HomeTeamName column from the Teams table
• AwayTeamName column from the Teams table

4. For the HomeTeamName and AwayTeamName columns, expand the Teams table and select the TeamName column.

5. In the Values section of the visual, add the Matchup measure from the Games table.

Hope this helps.

[cjd72consulting]

Thanks Sahir,

I may very well be missing something, but I don't think this works.

My Games Table has Team ID's, not Team Names, so your step 2 doesn't seem to make sense?

[Sahir_Maharaj]

@cjd72consulting, I might have misinterpretted your tables. Can you please provide more detail on:

• HomeTeamID
• AwayTeamID

Are these additional tables or do they relate to the Teams table?

[cjd72consulting]

@Sahir_Maharaj 

HomeTeamID and AwayTeamID are foreign keys referencing the Primary key TeamID in the Teams table