Dax formula help! filter and multiple items within string

[PBlearner27]

Hi everyone, 

• I have a table (Table 1) with the following data. Please note: The column "Group" has been created by merging (seperated by commas) three different columns (indicated with the three different colours). 

• I have another Table (Table 2) as below:

• I need a DAX formula to create the column outlined in red - "Sum of Price". The DAX formula needs to account for the following: 
  
  • Should include the filters Shop=A and Charges=ALL but excluding Discount (in Table 1).

(Please note: I cannot have the format of the tables changed/ I cannot use other types of visuals to achieve what I'm trying to do; I have checked all these options, I need it to be exactly as I've described above, thank you!!)

Any help would be greatly appreciated - thank you!

[lbendlin]

Acknowledging your note I am going to ignore it and tell you how I would resolve this.

Add a transform that splits the group column, and don't specify "into rows". You will end up with new columns Group.1, Group.2, and Group.3  which will immediately be usable for your Table2 desired outcome (through measures that apply your logic.

The only tricky part will be to add the blank rows.

[PBlearner27]

@lbendlin Thank you for your reply, however for my purpose I need to use a calculated column, I cannot use a measure. 

I have provided a very simplified example of what I'm trying to achieve - therefore it may not be very clear as to why I specifically need to use a calculated column. 

Is this not possible to do with a calculated column?

[Greg_Deckler]

@PBlearner27 - Well, I tend to agree with @lbendlin but if you are dead set on what you want, you can do it like this:

Column = 
    VAR __Item = [Shop A]
    VAR __Comma = FIND(",",__Item,,BLANK())
    VAR __First = IF(ISBLANK(__Comma),__Item,LEFT(__Item,__Comma - 1))
    VAR __Second = IF(ISBLANK(__Comma),BLANK(),RIGHT(__Item,LEN(__Item) - __Comma -1))
    VAR __Table = FILTER('Table (9)',CONTAINSSTRING([Group],__First) = TRUE() && CONTAINSSTRING([Group],__Second) = TRUE() && [Charges]<>"Discount")
RETURN
    IF(__Item = "",BLANK(),SUMX(__Table,[Price]))

I have attached a PBIX below my sig. You want Tables 9 and 10.

Next time, please post your data as text in a table so that we don't have to retype everything. Thanks. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[PBlearner27]

@Greg_Deckler I simply cannot thank you enough for this!! I am very grateful for your help, works like a charm, thank you so much!

I made a minor change by adding in a filter for "Shop A" as highlighted in the formula below.

Before I can mark it as a solution, there is one minor issue I'm unable to figure out:

• The rows highlighted in yellow should return values of £0 since these records don't exist in the filtered table - I'm not quite sure how to amend the formula for that, please can you help with this?

Thank you for your time - much appreciated!!

[Greg_Deckler · Accepted solution]

OK, this should work, I tweaked some things to make it act better:

Column = 
    VAR __Item = [Shop A]
    VAR __Comma = FIND(",",__Item,,BLANK())
    VAR __First = IF(ISBLANK(__Comma),__Item,LEFT(__Item,__Comma - 1))
    VAR __Second = IF(ISBLANK(__Comma),BLANK(),RIGHT(__Item,LEN(__Item) - __Comma -1))
    VAR __Table = 
        ADDCOLUMNS(
            FILTER('Table (9)',[Charges]<>"Discount" && [Shop] = "Shop A"),
            "__Include",IF(FIND(__First,[Group],,0) > 0 && FIND(__Second,[Group],,0) > 0,1,0)
        )
    VAR __Sum = SUMX(FILTER(__Table,[__Include]=1),[Price])
RETURN
    IF(__Item = "",BLANK(),IF(ISBLANK(__Sum),0,__Sum))

CONTAINSSTRING was causing me some grief so I went with FIND instead.

[PBlearner27]

@Greg_Deckler I am completely blown away!! Thank you so so much for your help Greg - this has saved me a lot of trouble and I've learnt something new & very useful!
Cheers 🙂