Combine two measures

[PSRai]

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

[helassal]

@PSRai 

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

[PSRai]

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].

[helassal]

@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.

[PSRai]

Thanks, Can't seem to get around the circular reference. any other suggestions?

[helassal]

@PSRai 

In that case, try creating it as a measure instead of a column. Can you try it like that:

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}, [EnergyUsedInPeriod1], [EnergyUsed]))

[PSRai]

@helassal 

Unfortuantely it returns the values of [Energy Used in Period1] but I get 0.0000 on [EnergyUsed].

[Energy Used in Period1], [EnergyUsed]

If I switch the two around  [EnergyUsed] and [Energy Used in Period1] then I get 0.000 on the [Energy Used in Period1]

[helassal · Accepted solution]

@PSRai 

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])))