Retrieve a value using a criteria on row and another on column

[Anonymous]

Hello,

here I come again with a complex DAX formula I need help with.

I have a source table like this : 

TABLE1

And I need to get into TABLE2 which already exists the data like follows : 

TABLE2

So I need a formula to put in "[TABLE2]Data column" which will go fetch the data filtering the row on the Name, and then filtering the column depending on the Q. Can someone help me figure out this formula ?

Regards

[rohit_singh · Accepted solution]

Hi @Anonymous ,

Based on what I can understand by looking at your tables, table 2 looks like an unpivoted version of table 1. 
Would it suit you if you performed an unpivot operation on Table 1 in power query. ? You'd get the values you need instantly

Please mark this answer as the solution if it resolves your issue.

Kind regards,

Rohit

[rohit_singh · Accepted solution]

Hi @Anonymous ,

Try this 

Table =

UNION(

SELECTCOLUMNS(Questions_base,"Name",[Name],"Q's","Q1","Answers",Questions_base[Q1]),

SELECTCOLUMNS(Questions_base,"Name",[Name],"Q's","Q2","Answers",Questions_base[Q2]),

SELECTCOLUMNS(Questions_base,"Name",[Name],"Q's","Q3","Answers",Questions_base[Q3]),

SELECTCOLUMNS(Questions_base,"Name",[Name],"Q's","Q4","Answers",Questions_base[Q4])

)

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!

Kind regards,

Rohit

[negi007]

@Anonymous  you can use unpivot table option in the power query window that will tranform your first table in the same format that you have in the second table. then you can merge table1 and table2. You wont need to write complex dax code.

go to your powerquery window, select all the columns (Q1 to Q4) and then unpivot them

once you have both tables in the same format. then you can use merge queries option to merge both the tables. you select both tables and then select merge queries. this all is possible in the power query window

[Anonymous]

I need to do it with DAX as the Q columns already are calculated columns in DAX

[rohit_singh · Accepted solution]

Hi @Anonymous ,

Based on what I can understand by looking at your tables, table 2 looks like an unpivoted version of table 1. 
Would it suit you if you performed an unpivot operation on Table 1 in power query. ? You'd get the values you need instantly

Please mark this answer as the solution if it resolves your issue.

Kind regards,

Rohit

[Anonymous]

I need it to do it with dax as the Q columns already are calculated columns in DAX

[negi007]

@Anonymous though not recommended to use dax to unpivot but you can try below link. similar problem was solved there as well

powerbi - Is it possible to unpivot in Power BI using DAX? - Stack Overflow

[Anonymous]

Well can we filter tables not only in rows but also columns ? I struggle to put the DAX code but I can do it easily in VBA

[rohit_singh · Accepted solution]

Hi @Anonymous ,

Try this 

Table =

UNION(

SELECTCOLUMNS(Questions_base,"Name",[Name],"Q's","Q1","Answers",Questions_base[Q1]),

SELECTCOLUMNS(Questions_base,"Name",[Name],"Q's","Q2","Answers",Questions_base[Q2]),

SELECTCOLUMNS(Questions_base,"Name",[Name],"Q's","Q3","Answers",Questions_base[Q3]),

SELECTCOLUMNS(Questions_base,"Name",[Name],"Q's","Q4","Answers",Questions_base[Q4])

)

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!

Kind regards,

Rohit

[Anonymous]

This creates a new table ? What I am looking for is a formula to put in the calculated column "Data column". I have other columns before I need to keep

[rohit_singh · Accepted solution]

Hi @Anonymous ,

Yes. You will use this new table as a lookup table to fetch values. This can be done as follows :

1. In the new table, create a new column using a combination of the name and q's fields 

2. Perform the same step on youir Table 2

3. Go to 'Manage relationships' and create a link between the new table and Table 2 using the combination key as the join.

4. Finally, go to Table 2, add a new column using the 'Related' function. This will lookup values from the new table and add them to Table 2

[Anonymous]

This worked perfectly. Thanks a lot !!