Forum Discussion

Frequent Visitor

2 years ago

Solved

Create a table using DAX

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...

Need Help

HotChilli

2 years ago

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.

HotChilli

Community Champion

2 years ago

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

Frequent Visitor

2 years ago

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