Need to create a Calculated table based off of two tables

[Anonymous]

Hi,

I need to create a Custom Table based off of two values from two different tables.

I have one table with one UNIQUE value (title_id)

I have another table with multiple values (Platform & Title_ID)

Basically what I need to do is create a new table based on the DISTINCT value of Platform that match to the Game ID specified in the other table.

Here is a picture of my end result in the hopes that it is better than my description, 

I have been able to create y table based off of the Distinct values of Platform in Table2 by using Table3=DISTINCT(Table2[Platform]) but I need to either have the gameID that references the platform (i.e. Table3 has 2 columns (Platform & GameID) or I need Table3 to only have the one column.

Hope that makes sense,

Thanks, Rich

[Vvelarde · Accepted solution]

@Anonymous

Hi, try with this:

Table3 = CALCULATETABLE(DISTINCT(Table2[Platform]),RELATEDTABLE(Table1)) 

[Vvelarde · Accepted solution]

@Anonymous

Hi, try with this:

Table3 = CALCULATETABLE(DISTINCT(Table2[Platform]),RELATEDTABLE(Table1)) 

[Anonymous]

Unfortunately, that did not work.  I am getting a column in my new table called PLATFORM but the value shows UNKNOWN:

[v-ljerr-msft]

Hi @Anonymous,

Based on my test, the formula provided by @Anonymous works fine against your sample data?

Is there any relationship between these two tables in your mode? Could you share a sample pbix file which can reproduce the issue, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[Anonymous]

Yes you are right. it works!!  I checked the relationships again and entered the calculated table function and it worked, 🙂

Thanks for all your help.

I have another question though which I dont think is possible... maybe through a filter?  But is there a way that I can have the [Game_ID] as a filter on my report, and depending on the game ID that has been filtered, it then changes the [Platform] values that are associated to that GameID?

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

No, it is not possible. Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections(like a slicer) on the report. 

Regards