Create a table using DAX

[Nerf_Herder]

Hi, 

I need to create a calculated table based on which customer name is manually added to a CustomerKeyFilter.

I'm trying to add three columns from table1 [CustomerKey], [QuestionId], [Answer] and three from table2 [GroupName], [WorkSpace], [QuestionLabel].

NewTable = 
VAR CustomerKeyFilter = "CustomerName"
RETURN
ADDCOLUMNS (
FILTER ( 'Table1', 'Table1'[CustomerKey] = CustomerKeyFilter ),
"QuestionId", 'Table1'[QuestionId],
"Answer", 'Table1'[Answer],
"WorkSpace",
CALCULATE (
MAXX (
FILTER ( 'Table2', RELATED('Table2'[id]) = RELATED('Table1'[QuestionId]) ),
'Table2'[WorkSpace]
)
),
"GroupName",
CALCULATE (
MAXX (
FILTER ( 'Table2', RELATED('Table2'[id]) = RELATED('Table1'[QuestionId]) ),
'Table2'[GroupName]
)
),
"QuestionLabel",
CALCULATE (
MAXX (
FILTER ( 'Table2', RELATED('Table2'[id]) = RELATED('Table1'[QuestionId]) ),
'Table2'[QuestionLabel]
)
)
)

It produces the following error:

The column 'Table2[id]' either doesn't exist or doesn't have a relationship to any table available in the current context.

The red squiggly line under 'Table1'[QuestionId] states:

Parameter is not the correct type.

Both columns, 'Table1'[QuestionId] and 'Table2'[id], are the correct names, they both exist and both data types are text.

The relationship between the two tables, via these two columns, is Many to one with a single cross-filter direction.

I've been looking for what seems like an eternity to find a solution which I can't seem to find.
I can't see what I'm doing wrong and as such, not sure how to fix it. Can someone help please.

[HotChilli · Accepted solution]

Hi, I think I've created the calculated table but I'm unsure if it meets the 'desired result' screenshots posted.

Here's the DAX:

NewTable2 = 
VAR CustomerKeyFilter = "CustomerName2"
RETURN
    ADDCOLUMNS (
        FILTER ( 'Table1', 'Table1'[CustomerKey] = CustomerKeyFilter ),
        "WorkSpace", RELATED('Table2'[WorkSpace] ),
        "GroupName", RELATED(Table2[GroupName] ),
        "QuestionLabel", RELATED('Table2'[QuestionLabel]  )
    )

 Let me know if that moves you on a bit further.

[Nerf_Herder]

This worked perfectly, thank you so much!

[HotChilli]

Can you change the access please so that i don't have to sign in?

[Nerf_Herder]

Done

[HotChilli]

Put the pbix on a 3rd party site (a legit one) and post the link please.

[Nerf_Herder]

I've uploaded it to google drive - https://drive.google.com/file/d/1Ud6cu8NJlSl_8mwUCXp8fMeD_goAi20c/view?usp=drive_link

[v-yifanw-msft]

Thank @HotChilli  for your prompt reply.

Hi @Nerf_Herder  ,

May I ask if your problem has been solved. If the problem is not yet solved, please feel free to ask us a question. Can you provide some example data such as detailed data (without sensitive information) and your relationships. This would allow us to better reproduce and solve the problem you are experiencing.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[HotChilli]

Can you post some sample data (just a few rows from each table please) and show what your desired result is.

It looks like there's too many RELATED keywords in there.

Can you post a picture of the relationship just to make sure I can understand what table keys are?

[Nerf_Herder]

Apologies for the delayed response, I've been AFW. Below is the sample data and desired results. I've put it all in a sample pbix file but I can't upload it here.

Table1:

id	CustomerKey	UID	QuestionId	Answer
1392c747-42e7-4ff2-82e9-63eb973a9396	CustomerName2	1392c747-42e7-4ff2-82e9-63eb973a9396/CustomerName2	6f5e51c8-b9c4-4840-b2da-c996d9e859f1	Answer1
4cde4441-2f5c-4d91-9a07-9dc4d61be1e5	CustomerName3	4cde4441-2f5c-4d91-9a07-9dc4d61be1e5/CustomerName3	320c927d-cf51-4c10-bdb7-ab7283061226	Answer2

Table2:

id	WorkSpace	GroupName	QuestionLabel	DesiredAnswer
534b0055-1b4d-4f34-8b42-ea933f78dc12	Workspace1	GroupName1	QuestionLabel1	DesiredAnswer1
320c927d-cf51-4c10-bdb7-ab7283061226	Workspace1	GroupName2	QuestionLabel2	DesiredAnswer2
6f5e51c8-b9c4-4840-b2da-c996d9e859f1	Workspace1	GroupName3	QuestionLabel3	DesiredAnswer3
d39d7fd3-2d82-4fb2-982d-47262ef20e0b	Workspace1	GroupName4	QuestionLabel4	DesiredAnswer4

Desired Results:

Slicer Selected:

Treemap Selected:

Treemap & Slicer Selected: