Create a Calculated Column on more than 2 arguments

Mischa · July 28, 2020, 3:54pm

Could somebody help me in writing the correct DAX code so that the Calculated Colum “Status” is populated with the values that come from the 2 columns AlliedForces and Germans in MilitaryGroups which serves as a LookUpTable. See also enclosed snap shot with all the details!

The end result shoud be 1 Calculated Column called status in the FactTable: BattleGroundActivity.
The lookup values have to come from 2 columns in the Table MilitaryGroups into 1 columns named Status in the FactTable: BattleGroundActivity

I made a start with the following DAX code. This is only a small part of the total DAX code, but already in this stadium it returns a False:
Status = AND(BattleGroundActivity[BattleKey]=SELECTEDVALUE(MilitaryGroups[BattleKey]); BattleGroundActivity[MililtaryGroup]=“AlliedForces”

Arguments I have to use are:

BattleKey
AlliedForces and Germans

Melissa · July 28, 2020, 4:07pm

Hi @Mischa,

Welcome to the forum.

I think you could sort this in Power Query but you can also do it in DAX
If you have a 1:M relationship between these tables, you can use RELATED

for example:

Status = 
 VAR myGroup = BattleGroudActivity[MilitaryGroup]
 RETURN

IF( myGroup = "Germans",
    RELATED( MilitaryGroup[Germans]),
    RELATED(MilitaryGroup[AlliedForces])
)

BrianJ · July 28, 2020, 4:12pm

@Mischa,

Welcome to the forum! Great to have you here.

Rather than via DAX/calculated columns, I think this task is handled far more easily via Power Query. Just start with your Lookup table, and reference that in Power Query to make a copy.