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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

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.