Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Alesch
Frequent Visitor

Customized column based on a certain row value

Dear community,

 

I am looking for a solution to flag/categorize all lines in a customer table in a "NewColumn". As soon as a customerID has a specific material, all lines relate to that customer should be flagged as visualized below:


CustomerIDMaterialNewColumn
1ADVADV Customer
1ViewerADV Customer
1EditorADV Customer
1WhateverADV Customer
2ENTENT Customer
2ViewerENT Customer
2EditorENT Customer
2WhateverENT Customer

 

Any help highly appreciated. 

1 ACCEPTED SOLUTION

Hi @Alesch ,

You can create a calculated column as below:

NewColumn = 
VAR _ADVcount =
    CALCULATE (
        COUNT ( 'Customers'[CustomerID] ),
        FILTER (
            ALL ( 'Customers' ),
            'Customers'[CustomerID] = EARLIER ( 'Customers'[CustomerID] )
                && 'Customers'[Material] = "ADV"
        )
    )
VAR _ENTcount =
    CALCULATE (
        COUNT ( 'Customers'[CustomerID] ),
        FILTER (
            ALL ( 'Customers' ),
            'Customers'[CustomerID] = EARLIER ( 'Customers'[CustomerID] )
                && 'Customers'[Material] = "ENT"
        )
    )
RETURN
    IF (
        _ADVcount > 0,
        "ADV Customer",
        IF (
            _ENTcount > 0,
            "ENT Customer",
            CONCATENATE ( 'Customers'[Material], " Customer" )
        )
    )

yingyinr_0-1626342913551.png

Best Regards

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

View solution in original post

6 REPLIES 6
SanketBhagwat
Solution Sage
Solution Sage

HI @Alesch .

You can use 

Custom = COMBINEVALUES(" ",'Sheet1 (2)'[Material],"Customer") .
 
You can also use;
CUSTOM = CONCATENATE('Sheet1 (2)'[Material],"Customer")
 
If this post answers your question, then please mark it as 'Accept as Solution' so that others could find it easily.

Hey @SanketBhagwat ,

 

thanks for answering here, your proposed solution results in something like this:

CustomerIDMaterialNewColumn
1ADVADV 1
1ViewerViewer 1
1EditorEditor 1
1WhateverWhatever 1
2ENTENT 2
2ViewerViewer 2
2EditorEditor 2
2WhateverWhatever 2

 

That is not what I am looking for. I really need all lines of a customer (with the same ID) where in one or more lines the material is ADV to be flagged as "ADV Customer".

 

You don't need to use Customer ID in here; 

Custom = COMBINEVALUES(" ",'Sheet1 (2)'[Material],"Customer").

It is a text and not a field.

Copy my formula as it is and see if it works.

 

 

But even this would concat whatever is in [Material] with the term customer. Wouldn´t that result in 

CustomerIDMaterialNewColumn
1ADVADV Customer
1ViewerViewer Customer
1EditorEditor Customer
1WhateverWhatever Customer
2ENTENT Customer
2ViewerViewer Customer
2EditorEditor Customer
2WhateverWhatever Customer



I need a solution where the term "ADV Customer" is applied to all lines for a certain customer as soon one (or more) of the materials is ADV.

Hi @Alesch ,

You can create a calculated column as below:

NewColumn = 
VAR _ADVcount =
    CALCULATE (
        COUNT ( 'Customers'[CustomerID] ),
        FILTER (
            ALL ( 'Customers' ),
            'Customers'[CustomerID] = EARLIER ( 'Customers'[CustomerID] )
                && 'Customers'[Material] = "ADV"
        )
    )
VAR _ENTcount =
    CALCULATE (
        COUNT ( 'Customers'[CustomerID] ),
        FILTER (
            ALL ( 'Customers' ),
            'Customers'[CustomerID] = EARLIER ( 'Customers'[CustomerID] )
                && 'Customers'[Material] = "ENT"
        )
    )
RETURN
    IF (
        _ADVcount > 0,
        "ADV Customer",
        IF (
            _ENTcount > 0,
            "ENT Customer",
            CONCATENATE ( 'Customers'[Material], " Customer" )
        )
    )

yingyinr_0-1626342913551.png

Best Regards

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

Thank you so much, I wasn´t able to check earlier. That did the trick!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.