Fill up a calculated column when matching conditions

[Anonymous]

So I'm trying to fill up one calculated column 'MainName' when my column 'Name' is within my column 'FullPath' and it has the 'ShortDescription' set to 'MAIN'

I did some researchs on internet and i was able to come to this formula:

IF(
    SEARCH(
        CALCULATE(
            FIRSTNONBLANK([Name];1);
            FILTER(
                ALL([TableName]);
                [ShortDescription]="MAIN"
            )
        );
        [FullPath]; 
        1; 
        0
    ) <> 0;
    CALCULATE(
        FIRSTNONBLANK([Name];1);
        FILTER(
            ALL([TableName]);
            [ShortDescription]="MAIN"
        )
    )
)

It works fine for just one MAIN tag. But i need this to work for any MAIN tag.

My source is something like this (i tried to put an image but i don't know how)
FULLPATH                     NAME                       SHORTDESCRIPTION                  MAINNAME
Test\Pbi                         Test                                    Main
Test2\siduhai                 Test2                                  Main
Test\lalala                      lalalala                               Others
Test2\duashiuh               help                                  Others

And i want the result to be something like this
FULLPATH                     NAME                       SHORTDESCRIPTION                  MAINNAME
Test\Pbi                         Test                                    Main                                        Test
Test2\siduhai                 Test2                                  Main                                        Test2
Test\lalala                      lalalala                               Others                                      Test
Test2\duashiuh               help                                  Others                                      Test2

Can anyone pls help me?

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, the ShortDescription column is just used to distinguish the "parents" and "children". It doesn't influence the calculation.  If so, you could reference my sample below:

• M: (in Query Editor)

= Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Fullpath],[Name]) then Text.BeforeDelimiter([Fullpath], "\") else null)

 

• DAX

Column =
VAR a =
    SEARCH ( "\", 'Table 1'[Fullpath],, 0 ) - 1
RETURN
    IF (
        SEARCH ( 'Table 1'[Name], 'Table 1'[Fullpath],, 0 ) > 0,
        LEFT ( 'Table 1'[Fullpath], a ),
        BLANK ()
    )

Best Regards,

Xue Ding

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

[Mariusz · Accepted solution]

Hi @Anonymous 

Also please consider this solution.

Power Query:

Dax Column:

Please see the script in the attached file.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Tara_]

Hi @Anonymous ,

Could you please confirm that this is what you are looking for just to clearify the result example you provided.

From the result example you provided, you are looking to fill the MAINNAME calculated column with the first word of the FULLPATH when the NAME is within FULLPATH OR the SHORTDESCRIPTION is set to MAIN (I put OR because in your result example you have added a MAINNAME even when the SHORTDESCRIPTION was Others). Is this correct?

[Anonymous]

@Tara_  Is not exactly this.

It is like the values that has the ShortDescription set to Others are "childrens" of the values that has the ShortDescription set to Main. You know?

What i'm trying to achieve is exactly this .. know who belongs to who to create an hierarchy view. And my only relation Parent -> Children is the FullPath and the ShortDescription. If ShortDescription is set to MAIN i know that it is the parent and i need to figure out the childrens. 

That's why i setted the MAINNAME of the rows that has Others (because they don't have MAIN ShortDescription so they're childrens), but it has nothing to do with the position of the MAIN in FULLPATH. 

Am I making myself clear? ahah

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, the ShortDescription column is just used to distinguish the "parents" and "children". It doesn't influence the calculation.  If so, you could reference my sample below:

• M: (in Query Editor)

= Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Fullpath],[Name]) then Text.BeforeDelimiter([Fullpath], "\") else null)

 

• DAX

Column =
VAR a =
    SEARCH ( "\", 'Table 1'[Fullpath],, 0 ) - 1
RETURN
    IF (
        SEARCH ( 'Table 1'[Name], 'Table 1'[Fullpath],, 0 ) > 0,
        LEFT ( 'Table 1'[Fullpath], a ),
        BLANK ()
    )

Best Regards,

Xue Ding

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