Should I use DAX or is there a better solution

[rpinxt]

I have this excel file :

So I have Cost Center (CC), Account (Acc) and Cost Type.

This is a manually maintained list (if a combi is Direct or Indirect)

Therefore of course not complete with all possible combinations.

It will be linked to a cost center field and a account field.

So all combi's should be ok.

But how can I cover everything?

When combi is not in I was thinking of adding all the cost centers with a blank account field and then make a default cost type.

(there are not that many cc's so that could manually be done)

However then I think I would need to make a measure that says if the combi is not there it should look at the cc and give that cost type?

Also to be it waterproof I think that the measure should also hold the option that if a cost center is not in it should give some sort of message (or N/A#).

[rpinxt]

Thanks but how would that help me with the cost type when the combination of cost center and account is not in?

As said the data can have hundreds of combinations cost center / account.

We only have maintained those that we need.

The other it should look to cost center and make it Direct or Inderect based on the manual maintained excel file.

[amitchandak]

@rpinxt , Not very clear, but you can create a dimension using crossjoin or generate

crossjoin(Distinct(Table[Acc]), Distinct(Table[costCenter]) )

You can have more columns as per need

Create a concatenated column and join it with your data table

Key = Table[Acc] & "-" & Table[costCenter]