The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have below table A-E & Ref1 & Ref2. Now i will need to calculate the Sum for each row based on column Ref1 & Ref2. it should take the value of Ref1 and match with columns from A-D, suppose D is value of Ref1 & C for ref2 then it should take from column C & D and give Sum of 100 + 300 = 400 in new calculated measure. I cannot hardcode the column names using Switch in DAX as they are dynamic and can change.
Table1
Output
Hi,
I am not sure if I understood your question correctly, but please check the attached pbix file.
expected result CC =
VAR _a =
ADDCOLUMNS (
VALUES ( 'Data'[ID] ),
"A", "A",
"@value", CALCULATE ( SUM ( 'Data'[A] ) )
)
VAR _b =
ADDCOLUMNS (
VALUES ( 'Data'[ID] ),
"A", "B",
"@value", CALCULATE ( SUM ( 'Data'[B] ) )
)
VAR _c =
ADDCOLUMNS (
VALUES ( 'Data'[ID] ),
"A", "C",
"@value", CALCULATE ( SUM ( 'Data'[C] ) )
)
VAR _d =
ADDCOLUMNS (
VALUES ( 'Data'[ID] ),
"A", "D",
"@value", CALCULATE ( SUM ( 'Data'[D] ) )
)
VAR _e =
ADDCOLUMNS (
VALUES ( 'Data'[ID] ),
"A", "E",
"@value", CALCULATE ( SUM ( 'Data'[E] ) )
)
VAR _t =
SELECTCOLUMNS (
UNION ( _a, _b, _c, _d, _e ),
"ID", 'Data'[ID],
"Ref", [A],
"@value", [@value]
)
RETURN
MAXX (
FILTER (
ADDCOLUMNS (
'Data',
"@sum",
SUMX (
FILTER (
_t,
'Data'[ID] = EARLIER ( 'Data'[ID] )
&& [Ref] = EARLIER ( 'Data'[Ref1] )
),
[@value]
)
+ SUMX (
FILTER (
_t,
'Data'[ID] = EARLIER ( 'Data'[ID] )
&& [Ref] = EARLIER ( 'Data'[Ref2] )
),
[@value]
)
),
Data[ID] = EARLIER ( Data[ID] )
),
[@sum]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks for your response. But the Issue is i cannot hardcode my query. it should do sometime like Hlookup funtionalitiy. take value D from ref1 Column and match with Column header A - E, and get D columns's row value. the issue is i cannot hardcode my values becuase column A-E may increase to A-Z...
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
63 | |
32 | |
21 | |
16 | |
15 |
User | Count |
---|---|
115 | |
33 | |
30 | |
24 | |
21 |