Forum Discussion
Combine two measures
I currently have 1 column = socket_id and 2 measures = Energy Used in Period1 and Energy Used,
I need to create a NEW COLUMN which is a mix of the two measures, Energy Used in Period1 and Energy Used based on socket_id
IF socket_id = 550, 639, 666, 667, 668, 669, 670, 671, 672, 673, 674, 675, 676, 679, 681, 682, 683 then use measure Energy in Period, for socket_id = 677, 678, 680
Would someone kindly help me create the NEW COLUMN
Socket_id Energy Used in Period1 Energy Used NEW COLUMN
550 0.01 1.441116111 0.01
639 0.01 1.394357111 0.01
666 0.9 2.671329556 0.9
667 0.87 93.18440033 0.87
668 0.89 7.578360556 0.89
669 0.09 15.27545489 0.09
670 106.58 1610.752961 106.58
671 283.57 1098.14139 283.57
672 65.96 246.8314083 65.96
673 80.12 91.071765 80.12
674 41.09 663.2624154 41.09
675 20.46 480.7403824 20.46
676 18.43 178.5269139 18.43
677 0.83 70.25492111 70.25492111
678 2.58 53.88401556 53.88401556
679 0.21 6.546997333 6.546997333
680 9.22 888.2489488 888.2489488
681 70.42 1047.141172 70.42
682 82.43 84.09463822 82.43
683 0.03 1.864957111 0.03
Total 784.7 6642.907904 1790.794883
Sorry I didn't get back to you earlier.
Please try to wrap the measures by a calculate.
New Measure = sumx('Table', if('Table'[Socket_id ] in {550, 639, 666, 667, 668, 669, 670, 671, 672, 673, 674, 675, 676, 679, 681, 682, 683}, Calculate([EnergyUsedInPeriod1]), Calculate([EnergyUsed])))
7 Replies
Can you try this out:
New Column = IF(socket_id in {550, 639, 666, 667, 668, 669, 670, 671, 672, 673, 674, 675, 676, 679, 681, 682, 683}, [Energy Used in Period 1], [Energy Used])This will use the "Energy Used In Period 1" for the socket ids mentioned between {} and will use "Energy Used" for all other socket ids.
If you are going to have more than those two cases, then I suggest to use Switch statement instead of If statement, like that:
New Column = Switch(True(), socket_id in {550, 639, 666, 667, 668, 669, 670, 671, 672, 673, 674, 675, 676, 679, 681, 682, 683}, [Energy Used in Period 1], socket_id in {677,678,680}, [Energy Used], socket_id in {n,n1,n2}, Measure3, Else Value)Using switch, you can add several conditions and have an "Else Value" in the end for any row that doesn't meet any of the conditions.
If Statement Reference: https://docs.microsoft.com/en-us/dax/if-function-dax
Swtich Statement Reference: https://docs.microsoft.com/en-us/dax/switch-function-dax
Best Regards
unfortunately I get the following error message
A circular dependency was detected: readings_true_energy[New Column], readings_true_energy[MaxEnrgy], readings_true_energy[New Column].
PSRai ,
I tried to reproduce the error, but it is working ok for me. Please check this sample file.
However, it is possible to have an underlying reason for the circular dependency, in that case, check this thread with a similar problem and how to attepmt to solve it.
Hope this helps.
