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.
Hi,
Nooby with DAX/PowerBI. In SQL I know how to do, but I could not figure out how to do in Power BI desktop. In the screenshot from Excel I explain what I will achieve. Also the steps I see what are needed for the calculation.
The point is that [c dim] is not in the pivot table. Therefore a SUMX does not have the correct context. [c dim] must be added in the calculation in order to calculate the correct amount in the pivot table.
Hope anyone can help!
Thanks in advance.
Hi @pdc,
Based on your current sample data, you can create a new table using the following formula.
Newtable = ADDCOLUMNS('C dimension',"v1",LOOKUPVALUE(FACT1[v1],FACT1[c dim],'C dimension'[c-dim]),"a dim",LOOKUPVALUE(FACT1[a dim],FACT1[c dim],'C dimension'[c-dim]))
Then create relationship between new table and FACT2 table using c dim field, and create a new column in the newly created table using formula below.
Column = 'Newtable'[v1]*RELATED(FACT2[v2])
This way, you can create a table visual as follows.
For more details about the above steps, please review this attached PBIX file.
Thanks,
Lydia Zhang
Hi Lydia,
Many thanks for your reply. I understand your solution. But I don't think this will solve my issue yet. If I'm correct the creation of Newtable is a static table during slice. This Newtable is refreshed during source data refresh. I need a dynamic table. I will explain why.
Based on your reply I understood my issue definition must be better. So I made a new screenshot, see below.
I added the g dim and r dim.
r dim was added because of a slicer, that excludes R1
g dim was added to show that v1 can be split into two values. Because there are a lot of records a summarize is required.
Does anyone have a solution? Many thanks in advance!
Hi @pdc,
In this sceanrio, you would need to create another table using the following formula.
Table = SUMMARIZE(FACT1,FACT1[a dim],FACT1[c dim],"v1",sum(FACT1[v1]))
Then create another new table based on the above table.
Newtable = ADDCOLUMNS('C dimension',"v1",LOOKUPVALUE('Table'[v1],'Table'[c dim],'C dimension'[c-dim]),"a dim",LOOKUPVALUE('Table'[a dim],'Table'[c dim],'C dimension'[c-dim]))
Please review modified PBIX file.
Thanks,
Lydia Zhang
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |