Need guide on dax measure

[NickProp28]

Dear Community,

Needs some advice from all of you, all I know is this can be done by using SWITCH

I have these raw data.

Condition is looking for the client (consignee/consignor) which one is unique.

If consignee and consignor both of it is unique, will return Consignee (For example C001, take consignee 'NIKE')

If conignee is unique, ie C002, return 'NIKE' . While C003 consignor is unique,return 'NIKE'

For C004, since there is no unique client. Return the consignor client name.

Expected outcome, 

Here is the pbix: https://ufile.io/dizaj4z6

I want to create the new column measure here. Can anyone give me a hints on the dax measure ?

Thanks for the help & attention on this matter 

[CNENFRNL]

@NickProp28 , the enumeration can be simplified as,

as long as CONSIGNEE is unique, the name of CONSIGNEE is returned; otherwise, CONSIGNOR is returned. So, the formula is also simple enough,

RESULT = 
IF (
    CALCULATE (
        DISTINCTCOUNT ( Client[Consignee] ),
        ALLEXCEPT ( Client, Client[ConsolNumber] )
    ) = 1,
    Client[Consignee],
    Client[Consignor]
)

[NickProp28]

Dear @CNENFRNL ,

Thanks for the code.

What if the condition 4. C004, at the end is taking consignee instead of consignor. 
What should I modify on your code?

[jaideepnema · Accepted solution]

Hi @NickProp28 ,

Create a calculated column like this:

Unique Match Column = 

var consignee=CALCULATE(DISTINCTCOUNTNOBLANK(Client[Consignee]),ALLEXCEPT(Client,Client[ConsolNumber]))

var consignor=CALCULATE(DISTINCTCOUNTNOBLANK(Client[Consignor]),ALLEXCEPT(Client,Client[ConsolNumber]))

var match=SWITCH(TRUE(),

AND(consignor=1,consignee=1),Client[Consignee],

consignee=1,Client[Consignee],

consignor=1,Client[Consignor],

Client[Consignor]

)

return match

or a measure like this to get the desired result:

Unique Match Measure = 

var consignee=CALCULATE(DISTINCTCOUNTNOBLANK(Client[Consignee]),ALLEXCEPT(Client,Client[ConsolNumber]))

var consignor=CALCULATE(DISTINCTCOUNTNOBLANK(Client[Consignor]),ALLEXCEPT(Client,Client[ConsolNumber]))

var match=SWITCH(TRUE(),

AND(consignor=1,consignee=1),FIRSTNONBLANK(Client[Consignee],TRUE()),

consignee=1,FIRSTNONBLANK(Client[Consignee],TRUE()),

consignor=1,FIRSTNONBLANK(Client[Consignor],TRUE()),

FIRSTNONBLANK(Client[Consignor],TRUE())

)

return match

In case you need the file here is the link:

https://we.tl/t-nSrfNwTkLV

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀

[amitchandak]

@NickProp28 , New measure

new measure =
var _1 = calculate(distinctcount([consignee]), filter(allselected(Table), [consolnumber] =max( [consolnumber] )))
var _2 = calculate(distinctcount([consignor]), filter(allselected(Table), [consolnumber] =max( [consolnumber] )))
return
Switch (True () ,
_1 = 1 , max(Table[consignee]),
_2 = 1 ,max(Table[consignor]) ,
max(Table[consignor])
)

or

new column =
var _1 = calculate(distinctcount([consignee]), filter(Table, [consolnumber] =earlier( [consolnumber] )))
var _2 = calculate(distinctcount([consignor]), filter(Table, [consolnumber] =earlier( [consolnumber] )))
return
Switch (True () ,
_1 = 1 , [consignee],
_2 = 1 ,[consignor] ,
[consignor]
)