• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional column with does NOT include and OR criteria

J

jutu

Member
I am adding a conditional column but it doesn't include the OR criteria. Is there a way around this. I read to create another column and then pivoting/unpivoting it but it doesn't seem to quite work and wanted to avoid creating it in DAX or M if possible. Tbh I don't think there is a simple way around it using a conditional column but you may have a magic trick around it? Thank you

This is the SQL query for it which I need to put in PBI, but not very good using SWITCH me and would rather use the conditional column option if at all possible;

WHEN LET.[LETCODE_CODE]

IN ('B1CU|STMTOFACCT','GBW|BILLPRINT|CSV','S1STMT|ACCTSUMMARY','S1STMT|STMT')

THEN '1. Statement or Summary'

WHEN LEFT(LET.[LETCODE_CODE],8) = 'S1CC|1ST'

THEN '2. CC 1st Reminder'

WHEN LEFT(LET.[LETCODE_CODE],8) = 'S1CC|2ND' OR LET.[LETCODE_CODE] = 'S1CC|CLOSEDACCTDCA'

THEN '3. CC 2nd Reminder'

WHEN LEFT(LET.[LETCODE_CODE],7) = 'S1CC|IC'

THEN '4. Late Payment Reminder'

WHEN LEFT(LET.[LETCODE_CODE],16) = 'GBWCUS|COMPLAINT'

THEN '5. Complaint Correspondence'

WHEN LET.[LETCODE_CODE] IN ('S1MOVE|WELCOMECONF','S1ACQACT|WLCMPK1','S1ACQACT|WLCMPK2')

THEN '6. Welcome Pack or Letter'

WHEN LEFT(LET.[LETCODE_CODE],5) = 'S1DD|'

THEN '7. Direct Debit Letters'

WHEN LEFT(LET.[LETCODE_CODE],7) = 'S1LOSS|'

THEN '8. Customer Loss Letters'

ELSE '9. Other Communications'

END AS 'LETTER_CLASS'
 
Hello Jutu

In Power BI, you can create a custom column using the "Add Column" tab in the Power Query Editor. While Power Query doesn't have a direct equivalent to the SQL CASE statement, you can achieve similar functionality using the following steps:

1. Open the Power Query Editor.
2. Select the table you want to add the conditional column to.
3. Go to the "Add Column" tab.
4. Choose "Custom Column."
5. Enter a name for your new column (e.g., "LETTER_CLASS").
6. Use the following expression:

Code: 
=if List.Contains({"B1CU|STMTOFACCT","GBW|BILLPRINT|CSV","S1STMT|ACCTSUMMARY","S1STMT|STMT"}, [LETCODE_CODE]) then
        "1. Statement or Summary"
    else if Text.Start([LETCODE_CODE], 8) = "S1CC|1ST" then
        "2. CC 1st Reminder"
    else if Text.Start([LETCODE_CODE], 8) = "S1CC|2ND" or [LETCODE_CODE] = "S1CC|CLOSEDACCTDCA" then
        "3. CC 2nd Reminder"
    else if Text.Start([LETCODE_CODE], 7) = "S1CC|IC" then
        "4. Late Payment Reminder"
    else if Text.Start([LETCODE_CODE], 16) = "GBWCUS|COMPLAINT" then
        "5. Complaint Correspondence"
    else if List.Contains({"S1MOVE|WELCOMECONF","S1ACQACT|WLCMPK1","S1ACQACT|WLCMPK2"}, [LETCODE_CODE]) then
        "6. Welcome Pack or Letter"
    else if Text.Start([LETCODE_CODE], 5) = "S1DD|" then
        "7. Direct Debit Letters"
    else if Text.Start([LETCODE_CODE], 7) = "S1LOSS|" then
        "8. Customer Loss Letters"
    else
        "9. Other Communications"

This expression is a nested if-else statement that replicates the logic you have in your SQL query. Adjust the column and table names as per your actual Power BI dataset. After creating the column, you can use it in your visualizations.
 
Brilliant! Thank you so much :)
Though it takes very long to load it and I am also trying using SWITCH but no success so far. I would appreciate if you can help me with the SWITCH function to translate the same SQL code into a DAX formula using SWITCH. Thank you
 
I have put the below together so far and it seems to accept no errors but brigs the error 'Too many arguments were passed to the CONTAINSSTRING function', underlying in red only the '[LETCODE_CODE]' as an error, not the table. All the LETCODE_CODEs are red underlined. That field is correct and not sure why it's giving me that error;


LETTERCODE_CLASS DAX =
SWITCH(
TRUE(),
CONTAINSSTRING({"B1CU|STMTOFACCT","GBW|BILLPRINT|CSV","S1STMT|ACCTSUMMARY","S1STMT|STMT"}, 'StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE]), "1. Statement or Summary",
LEFT('StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE], 8) = "S1CC|1ST", "2. CC 1st Reminder",
LEFT('StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE], 8) = "S1CC|2ND" || 'StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE] = "S1CC|CLOSEDACCTDCA", "3. CC 2nd Reminder",
LEFT('StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE], 7) = "S1CC|IC", "4. Late Payment Reminder",
LEFT('StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE], 16) = "GBWCUS|COMPLAINT", "5. Complaint Correspondence",
CONTAINSSTRING({"S1MOVE|WELCOMECONF","S1ACQACT|WLCMPK1","S1ACQACT|WLCMPK2"}, 'StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE]), "6. Welcome Pack or Letter",
LEFT('StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE], 5) = "S1DD|", "7. Direct Debit Letters",
LEFT('StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE], 7) = "S1LOSS|", "8. Customer Loss Letters",
TRUE(), "9. Other Communications"
 
The actual error given is 'A single value for column 'LETCODE_CODE cannot be determined. So, it's not as if it's not found the record but that it's not a single value? The actual value is an string for context purpose
 
Last edited:
jutu said:
I have put the below together so far and it seems to accept no errors but brigs the error 'Too many arguments were passed to the CONTAINSSTRING function', underlying in red only the '[LETCODE_CODE]' as an error, not the table. All the LETCODE_CODEs are red underlined. That field is correct and not sure why it's giving me that error;


LETTERCODE_CLASS DAX =
SWITCH(
TRUE(),
CONTAINSSTRING({"B1CU|STMTOFACCT","GBW|BILLPRINT|CSV","S1STMT|ACCTSUMMARY","S1STMT|STMT"}, 'StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE]), "1. Statement or Summary",
LEFT('StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE], 8) = "S1CC|1ST", "2. CC 1st Reminder",
LEFT('StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE], 8) = "S1CC|2ND" || 'StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE] = "S1CC|CLOSEDACCTDCA", "3. CC 2nd Reminder",
LEFT('StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE], 7) = "S1CC|IC", "4. Late Payment Reminder",
LEFT('StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE], 16) = "GBWCUS|COMPLAINT", "5. Complaint Correspondence",
CONTAINSSTRING({"S1MOVE|WELCOMECONF","S1ACQACT|WLCMPK1","S1ACQACT|WLCMPK2"}, 'StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE]), "6. Welcome Pack or Letter",
LEFT('StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE], 5) = "S1DD|", "7. Direct Debit Letters",
LEFT('StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE], 7) = "S1LOSS|", "8. Customer Loss Letters",
TRUE(), "9. Other Communications"
Click to expand...
Hey Jutu

I think, try this line replacing

Code: 
CONTAINSSTRING({"B1CU|STMTOFACCT","GBW|BILLPRINT|CSV","S1STMT|ACCTSUMMARY","S1STMT|STMT"}, 'StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE])

Replace to this

Code: 
CONTAINSSTRING("B1CU|STMTOFACCT|GBW|BILLPRINT|CSV|S1STMT|ACCTSUMMARY|S1STMT|STMT", 'StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE])

Make sure to concatenate all the values into a single text string separated by the pipe character (|). Adjust this part for other similar CONTAINSSTRING functions in your SWITCH statement
 
Hi Monty thank you for the above. I replaced all the instances with a single double quotation marks in the whole string as per your example but still throwing the same error;
'a singe value for column 'LETCO_CODE' cannot be determined and also states that this may happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count or sum to get a single result.'

Not sure if this helps but this is the error that it's giving
 
jutu said:
Hi Monty thank you for the above. I replaced all the instances with a single double quotation marks in the whole string as per your example but still throwing the same error;
'a singe value for column 'LETCO_CODE' cannot be determined and also states that this may happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count or sum to get a single result.'

Not sure if this helps but this is the error that it's giving
Click to expand...
Hello Jutu

Let take another approach, i think It appears that the issue might be related to the way the `CONTAINSSTRING` function is used within the `SWITCH` statement. To resolve this error, you should modify the usage of `CONTAINSSTRING` so that it works correctly within the context of your data model.

Here's an updated version of your DAX expression:

Code: 
LETTERCODE_CLASS DAX =
SWITCH(
    TRUE(),
    CONTAINSSTRING("B1CU|STMTOFACCT|GBW|BILLPRINT|CSV|S1STMT|ACCTSUMMARY|S1STMT|STMT", 'StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE]), "1. Statement or Summary",
    LEFT('StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE], 8) = "S1CC|1ST", "2. CC 1st Reminder",
    LEFT('StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE], 8) = "S1CC|2ND" || 'StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE] = "S1CC|CLOSEDACCTDCA", "3. CC 2nd Reminder",
    LEFT('StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE], 7) = "S1CC|IC", "4. Late Payment Reminder",
    LEFT('StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE], 16) = "GBWCUS|COMPLAINT", "5. Complaint Correspondence",
    CONTAINSSTRING("S1MOVE|WELCOMECONF|S1ACQACT|WLCMPK1|S1ACQACT|WLCMPK2", 'StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE]), "6. Welcome Pack or Letter",
    LEFT('StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE], 5) = "S1DD|", "7. Direct Debit Letters",
    LEFT('StagingVelocity LET_FORMATDATAARC'[LETCODE_CODE], 7) = "S1LOSS|", "8. Customer Loss Letters",
    TRUE(), "9. Other Communications"
)


In this modified version, I've removed the curly braces and used a single string for each `CONTAINSSTRING` function. Make sure that the concatenated string for `CONTAINSSTRING` includes all the possible values separated by the pipe character (`|`). Adjust this format for other similar `CONTAINSSTRING` functions in your `SWITCH` statements.Give a short and let me know.
 
Hi. Still giving the same error sorry. It always underlines the same field of 'LETCODE_CODE'.

Error is;
"A single value for column 'LETCODE_CODE' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation"
 
How/where are you trying to use this in Power BI?

(I'm still confused by your original statement that you wanted to try and avoid M and DAX - that really doesn't leave any options)
 
Ok yes you are right. I thought that DAX would take less space/loading time but an added column works well. Thank you
 
You must log in or register to reply here.
Back
Top