Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Hi @Anonymous ,
Try this
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!
Kind regards,
Rohit
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 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
Proud to be a Super User!
I need to do it with DAX as the Q columns already are calculated columns in DAX
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
I need it to do it with dax as the Q columns already are calculated columns in DAX
@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
Proud to be a Super User!
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
Hi @Anonymous ,
Try this
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!
Kind regards,
Rohit
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
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
This worked perfectly. Thanks a lot !!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |