Forum Discussion

NickProp28's avatar
NickProp28
Icon for Post Partisan rankPost Partisan
5 years ago
Solved

Nees help on the DAX

Dear Community,

Here is the example dataset for my report, there have total 10 condition I need to apply in DAX in order to get the result.

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],
AND(ISBLANK(consignor),consignee<>1),"BLANK",
AND(ISBLANK(consignee),consignor<>1),"BLANK",
consignee=1,Client[Consignee],
consignor=1,Client[Consignor],
"MIX")
return match
I face some problem in dax when applying the rules for C009 and C010. There have a result null because DAX is referring to the column consignee/consignor when the condition is hit. 


I would like to request some help on modify the DAX, for C009 and C010, if the consignee have only one brand name but also having null value, the result is return to the brand name without the null value. 
For example, 

 



But if there have more than one brand name with null value, the result will return 'BLANK'

For example,

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

Appreciate any helps provided & thanks for your attention. 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    NickProp28 , done few changes. If needed you can test by reverting some >= changes

     

    Unique Match Column = 
var consignee=CALCULATE(DISTINCTCOUNTNOBLANK(Client[Consignee]),ALLEXCEPT(Client,Client[ConsolNumber]))
var consignor=CALCULATE(DISTINCTCOUNTNOBLANK(Client[Consignor]),ALLEXCEPT(Client,Client[ConsolNumber]))
var consignee1=CALCULATE(Max(Client[Consignee]),ALLEXCEPT(Client,Client[ConsolNumber]))
var consignor1=CALCULATE(Max(Client[Consignor]),ALLEXCEPT(Client,Client[ConsolNumber]))
var match=SWITCH(TRUE(),
AND(consignor>=1,consignee>=1),consignee1,
consignee>=1,consignee1,
consignor>=1,consignor1,
AND(ISBLANK(consignor),consignee<>1),"BLANK",
AND(ISBLANK(consignee),consignor<>1),"BLANK",

"MIX")
return match

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    NickProp28 , I think moving last two before should help  like

     

    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],
AND(ISBLANK(consignor),consignee<>1),blank(),
AND(ISBLANK(consignee),consignor<>1),blank(),
"MIX")
return match
      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

        NickProp28 , done few changes. If needed you can test by reverting some >= changes

         

        Unique Match Column = 
var consignee=CALCULATE(DISTINCTCOUNTNOBLANK(Client[Consignee]),ALLEXCEPT(Client,Client[ConsolNumber]))
var consignor=CALCULATE(DISTINCTCOUNTNOBLANK(Client[Consignor]),ALLEXCEPT(Client,Client[ConsolNumber]))
var consignee1=CALCULATE(Max(Client[Consignee]),ALLEXCEPT(Client,Client[ConsolNumber]))
var consignor1=CALCULATE(Max(Client[Consignor]),ALLEXCEPT(Client,Client[ConsolNumber]))
var match=SWITCH(TRUE(),
AND(consignor>=1,consignee>=1),consignee1,
consignee>=1,consignee1,
consignor>=1,consignor1,
AND(ISBLANK(consignor),consignee<>1),"BLANK",
AND(ISBLANK(consignee),consignor<>1),"BLANK",

"MIX")
return match