SWITCH STATEMENT IN DAX

 Hi Friends!

Spent the whole day in understanding how to implement the switch statement in Direct query , tabular model. 

I finally found workaround for it. Include the switch statement in filter columns. I have used EXCEPT to exclude the rows that I don't need

CALCULATE(DISTINCTCOUNT(D_SF_VIP_ACCOUNT[CD_VIP_ACCOUNT]),

EXCEPT

(

FILTER(

SUMMARIZE(D_SALES_STATE_HIST,

D_SALES_STATE_HIST[CD_SYSCOUNTRY],

D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE],

D_SF_VIP_ACCOUNT[CD_NATIONALITY],

"VT", DISTINCTCOUNT(D_SF_VIP_ACCOUNT[CD_VIP_ACCOUNT])),

(D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "CHN") ),

FILTER(

SUMMARIZE(D_SALES_STATE_HIST,

D_SALES_STATE_HIST[CD_SYSCOUNTRY],

D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE],

D_SF_VIP_ACCOUNT[CD_NATIONALITY],

"VT", DISTINCTCOUNT(D_SF_VIP_ACCOUNT[CD_VIP_ACCOUNT])),

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "AUS" && D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "NZL") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "AUS" && D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "AUS") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "THD" && D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "THA") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "KLM" && D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "KOR") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "HKG" && D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "MAC") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "HKG" && D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "HKG") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "HKG" && D_SF_VIP_ACCOUNT[CD_NATIONALITY] = "MAC") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "HKG" && D_SF_VIP_ACCOUNT[CD_NATIONALITY] = "HKG") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE]) ||

ISBLANK(D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE])

)

)

Be Happy !