cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Calculated Column DAX Help

I am stuck with a calculated column creation, can anyone help -- 

 

Here is my SQL & DAX Codes for a CALCULATED COLUMN.

 

SQL Code -

CASE WHEN HEADER_TABEL.REGION='AMERICA' AND DETAIL_TABLE.TRANS_CODE='CANCEL' THEN 'CANCELLED' ELSE COALESCE(COALESCE(RIGHT(FINANCE.SUBSCRIPTION_STATUS_M,CHARACTER_LENGTH(FINANCE.SUBSCRIPTION_STATUS_M)-1),RIGHT(FINANCE_T.SUBSCRIPTION_STATUS_M,CHARACTER_LENGTH(FINANCE_T.SUBSCRIPTION_STATUS_M)-1)),'NOT CANCELLED') END AS SUBSCRIP_STATUS

 

DAX i tried --

 

SWITCH(TRUE(),
'HEADER_TABEL'[REGION]="AMERICA && MAX('DETAIL_TABLE'[TRANS_CODE])="CANCEL","CANCELLED",
COALESCE(COALESCE(RIGHT('HEADER_TABEL'[SUBSCRIPTION_STATUS_M],LEN('HEADER_TABEL'[SUBSCRIPTION_STATUS_M])-1),
RIGHT('HEADER_TABEL'[SUBSCRIPTION_STATUS_M_T],LEN('HEADER_TABEL'[SUBSCRIPTION_STATUS_M_T])-1)),"NOT CANCELLED"),
BLANK())

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: Calculated Column DAX Help

@MKPBI_2805 

I think in your case a simple IF will be more appropriate solution

IF(

'HEADER_TABEL'[REGION]="AMERICA && MAX('DETAIL_TABLE'[TRANS_CODE])="CANCEL", 
"CANCELLED",
COALESCE(
RIGHT('HEADER_TABEL'[SUBSCRIPTION_STATUS_M],LEN('HEADER_TABEL'[SUBSCRIPTION_STATUS_M])-1),
RIGHT('HEADER_TABEL'[SUBSCRIPTION_STATUS_M_T],LEN('HEADER_TABEL'[SUBSCRIPTION_STATUS_M_T])-1),
"NOT CANCELLED"
)

)

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

1 REPLY 1
Highlighted
Super User VI
Super User VI

Re: Calculated Column DAX Help

@MKPBI_2805 

I think in your case a simple IF will be more appropriate solution

IF(

'HEADER_TABEL'[REGION]="AMERICA && MAX('DETAIL_TABLE'[TRANS_CODE])="CANCEL", 
"CANCELLED",
COALESCE(
RIGHT('HEADER_TABEL'[SUBSCRIPTION_STATUS_M],LEN('HEADER_TABEL'[SUBSCRIPTION_STATUS_M])-1),
RIGHT('HEADER_TABEL'[SUBSCRIPTION_STATUS_M_T],LEN('HEADER_TABEL'[SUBSCRIPTION_STATUS_M_T])-1),
"NOT CANCELLED"
)

)

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors