cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brookied
Helper I
Helper I

DAX help needed

Afternoon All, 

I need a little help to get the below working as expected. 

 

 I have a table of income data,  this would inclusde

Amount = Various values

Payment Date = date of payment 

SourceCode  = Structured source code to track payments

Payment Method = Cash,  Direct Debit etc..

 

I have a custom column" that for the most par works, Everything below execpt the section in bold works.   What i want to happen is 

this exceprt from SQL

 

"case

when sourcecode like 'A%' and sourcecode not in ('AUGO','AUPO','AURO','AUTO','AUCO','AUFO','OOOO','OAMA','OANA',

    'OARS','ZREF','ZBAN','NAUG','AWAO','BUSR') and sourcecode not like 'AV%' then '01_Cash'

"

 

I constructed the par in bold and while no errors i dont seem to see in my SourceType Column the CASH value returned?

 

IF anyone has any pointers please fire th

 

SOURECTYPE =
IF(
'Fundraising Inc Report'[SOURCE_LETTER] = "A" && 'Fundraising Inc Report'[Payment Method]="TAX",
"TAX",IF(
'Fundraising Inc Report'[SOURCE_LETTER] = "A" && 'Fundraising Inc Report'[Payment Method]<>"TAX",
"Appeals",IF(
'Fundraising Inc Report'[SOURCE_LETTER] = "C",
"Corporate",IF(
'Fundraising Inc Report'[SOURCE_LETTER] = "E",
"Emergencies",IF(
'Fundraising Inc Report'[SOURCE_LETTER] = "L",
"Legacy",IF(
'Fundraising Inc Report'[SOURCE_LETTER] = "M",
"Major Donor",IF(
'Fundraising Inc Report'[SOURCE_LETTER] = "R",
"Community",IF(
'Fundraising Inc Report'[SOURCE_LETTER] = "U",
"Unattributable",IF(
'Fundraising Inc Report'[SOURCE_LETTER] = "Z",
"Refunds/Fees", IF(
'Fundraising Inc Report'[SOURCE_LETTER] = "A" && NOT
(SEARCH("AUGO", 'Fundraising Inc Report'[Source Code (Source Campaign)] ,, 0 ) <> 0 ||
SEARCH("AUPO", 'Fundraising Inc Report'[Source Code (Source Campaign)] ,, 0 ) <> 0 ||
SEARCH("AURO", 'Fundraising Inc Report'[Source Code (Source Campaign)] ,, 0 ) <> 0 ||
SEARCH("AUTO", 'Fundraising Inc Report'[Source Code (Source Campaign)] ,, 0 ) <> 0 ||
SEARCH("AUCO", 'Fundraising Inc Report'[Source Code (Source Campaign)] ,, 0 ) <> 0 ||
SEARCH("AUFO", 'Fundraising Inc Report'[Source Code (Source Campaign)] ,, 0 ) <> 0 ||
SEARCH("OOOO", 'Fundraising Inc Report'[Source Code (Source Campaign)] ,, 0 ) <> 0 ||
SEARCH("OAMA", 'Fundraising Inc Report'[Source Code (Source Campaign)] ,, 0 ) <> 0 ||
SEARCH("OANA", 'Fundraising Inc Report'[Source Code (Source Campaign)] ,, 0 ) <> 0 ||
SEARCH("OARS", 'Fundraising Inc Report'[Source Code (Source Campaign)] ,, 0 ) <> 0 ||
SEARCH("ZREF", 'Fundraising Inc Report'[Source Code (Source Campaign)] ,, 0 ) <> 0 ||
SEARCH("ZBAN", 'Fundraising Inc Report'[Source Code (Source Campaign)] ,, 0 ) <> 0 ||
SEARCH("NAUG", 'Fundraising Inc Report'[Source Code (Source Campaign)] ,, 0 ) <> 0 ||
SEARCH("AWAO", 'Fundraising Inc Report'[Source Code (Source Campaign)] ,, 0 ) <> 0 ||
SEARCH("BUSR", 'Fundraising Inc Report'[Source Code (Source Campaign)] ,, 0 ) <> 0 ||
SEARCH("AUGO", 'Fundraising Inc Report'[Source Code (Source Campaign)] ,, 0 ) <> 0 ||
SEARCH("AV**", 'Fundraising Inc Report'[Source Code (Source Campaign)] ,, 0 ) <> 0), "Cash",
 
IF('Fundraising Inc Report'[SOURCE_LETTER] = "0",
"000 Various", "MISC"
)
))))))))))

 

1 ACCEPTED SOLUTION
ryan_mayu
Super User II
Super User II

@Brookied 

have you tried

IF(LEFT(SOUCECODE,1)="A" && NOT SOURCECODE IN {"XXX","XXX","XXX"}&&LEFT(SOURCECODE,2)<>"AV", "01_CASH")





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
ryan_mayu
Super User II
Super User II

@Brookied 

have you tried

IF(LEFT(SOUCECODE,1)="A" && NOT SOURCECODE IN {"XXX","XXX","XXX"}&&LEFT(SOURCECODE,2)<>"AV", "01_CASH")





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

@ryan_mayu 

I have not so let me rebuild based on above syntax and will let you know,   Thanks

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors