Add Column Apply to Each Row with Multiple Criteria

[Spudonis]

Good afternoon,

**Edit**

I'm looking for something similar to this

Fiber = IF ('Table'[Customer ID] IN {IF (FIND ("Fiber", 'Table'[Plan Name],1,BLANK()), 'Table'[Customer ID], BLANK())}, "Y", "N")

I need it to just be an add column function rather than creating a table if possible. The above formula doesn't quie work as it doesn't add "Y" to the Fiber column if the plan name is something without the word "Fiber" in it.

**End Edit**

I'm still fairly new to Power BI and DAX. I'm not really sure what all it can fully do but this is the software I've been asked to work with. I know what I want it to do and hope the community can help me.

Starting Data:

Customer ID	Plan Name	Plan Type
23	Fiber 100	DATA
37	Wireless 30	DATA
68	Cable 50	DATA
71	Fiber 70	DATA
99	Cable 00	DATA
23	VOIP 10	VOICE
23	Taxes 11	TAX
37	Taxes 12	TAX
68	Taxes 13	TAX
71	Taxes 14	TAX
99	Taxes 15	TAX
23	Discount 2	Recurring
37	Discount 3	Recurring
68	Discount 5	Recurring

I need to add a column that will locate all accounts that contains the criteria in Plan Name and have the new column add a new text to all instances of that Customer ID.

Example Output:

Customer ID	Plan Name	Plan Type	Fiber
23	Fiber 100	DATA	Y
37	Wireless 30	DATA	N
68	Cable 50	DATA	N
71	Fiber 70	DATA	Y
99	Cable 00	DATA	N
23	VOIP 10	VOICE	Y
23	Taxes 11	TAX	Y
37	Taxes 12	TAX	N
68	Taxes 13	TAX	N
71	Taxes 14	TAX	Y
99	Taxes 15	TAX	N
23	Discount 2	Recurring	Y
37	Discount 3	Recurring	N
68	Discount 5	Recurring	N

I've been pondering this all day but had no luck in figuring it out. There are some 30k+ rows of data so doing any of this by hand is a no-go.

Any help is greatly appreciated.

-Spudonis

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution ToddChitt  offered, and i want to offer some more information for user to refer to.

hello @Spudonis , you can create a calculated column.

Column =
VAR a =
    SUMMARIZE (
        FILTER ( 'Table', CONTAINSSTRING ( [Plan Name], "Fiber" ) ),
        [Customer ID]
    )
RETURN
    IF ( [Customer ID] IN a, "Y", "N" )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Spudonis]

Thank you very much for this! I modified it slightly due to needing it to be case sensitive for the search but this worked wonders!

My code (thanks to you)

Fiber=

VAR a =

    SUMMARIZE(

        FILTER('Table',ISNUMBER(FIND("Fiber",'Table'[Plan Name],1,BLANK()))),'Table'[Customer ID]

    )

RETURN

    IF('Table'[CustomerID] IN a, "Y", "N")

[ToddChitt]

Investigate the following DAX functions: FILTER, SEARCH, and SELECTCOLUMNS.

SELECTCOLUMNS allows you to create a table using specific columns from another table. That 'table' can be a FILTER statement, and that FILTER statement can use the SEARCH function to look for "Fiber" in the appropriate field.

The SEARCH will return the ordinal position of the search term, if it exists. 

Now use the FILTER to return only a set of rows where the SEARCH results in not null.

Use SELECTCOLUMNS to get the Customer ID from the result of the FILTER statement.

My Fiber Customers = SELECTCOLUMNS( FILTER ( [Customers], (IF( SEARCH ( "Fiber", [Plan Name], 1, BLANK() ) ) >= 1, [Customer ID], "Fiber Customer ID" )

(sorry not sure of the exact DAX syntax here)

The end result will be a table of Customers that have "Fiber" as a part of the Plan Name. Join that to the table you have on Customer ID, and set up an IF statement using RELATED.:

The calculated column could be:

[Fiber] = IF ( ISBLANK ( RELATED ( 'My Fiber Customers'[Fiber Customer ID] ) ), "N", "Y" )

Hope that helps. Learn about those DAX funtions here:

SEARCH function (DAX) - DAX | Microsoft Learn

[Spudonis]

I edited my initial post. Hopefully I've added some more clarity to what I'm after.

[ToddChitt]

Is this a simple Calculated Column in DAX? Like IF / THEN / ELSE ?

Can you share the logic that would make cetain rows a "Y" and others "N"? Is it based on data from another table?

Just looking at it, I cannot discern what makes a Y versus N. We need more of your business logic.

[ToddChitt]

Is this an attribut of the Customer? Seems only Customer ID of 23 and 71 are marked Y, all the others N.

Is it as simple as:

[Fiber] = IF ([Customer ID] IN {23, 71 }, "Y", "N" )

?

[Spudonis]

I couldn't use that formula as there are thousands of account IDs I wouldn't know beforehand which ones would contain what plan name.

[Spudonis]

Apologies. I had that included in the post originally but the forum gave an error and removed it.

I'm trying to have it search for any row that contains the word "Fiber" and then make the new column "Y" if it is true but it has to apply that "Y" to all instances of that Customer ID.

It might be a simple IF, THEN, ELSE but I don't know enough to know if that would work or not. I couldn't get it to work to apply to each row with that account ID.