How can I create this Measure?

[Anonymous]

Hello

I have a Table1 visual with Col1 and Col2 below:

A, A1

B, B1

etc

The A1, B1 values are common with Col1 of another Table2 and I used that column to join the two tables.

The A, B values exist in a Col2 of the Table2 as well, but in the form of:

A | B | C

B | C

A

A | B

etc

I.e. the A, B values appear in the Table2 but they can be part of a larger string joined with "|".

My goal is to create a count measure that will count the rows in Table2 with the following matches:

1) the count will match the common values A1, B1 etc in Table2

2) the count will match the non-directly-common (as they are in a string joined with "|") values A, B, in a way that if e.g. A is contained in the "|" joined string, it should be matched.

How can I do that?

Thanks!

[Icey · Accepted solution]

Hi @Anonymous ,

Please check:

Create measures like so:

Count 1 = 
VAR t =
    ADDCOLUMNS (
        Table2,
        "Col2_", LOOKUPVALUE ( Table1[Col1], Table1[Col1], Table2[Col2] )
    )
RETURN
    IF (
        HASONEVALUE ( Table1[Col1] ),
        COUNTROWS (
            FILTER ( t, [Col2_] <> BLANK () && [Col2_] = MAX ( Table1[Col1] ) )
        ) + 0,
        COUNTROWS ( FILTER ( t, [Col2_] <> BLANK () ) ) + 0
    )

Count 2 = 
VAR t =
    ADDCOLUMNS (
        Table2,
        "Col2_", IF (
            SEARCH ( MAX ( Table1[Col1] ), Table2[Col2],, 0 ) <> 0
                && SEARCH ( "|", Table2[Col2],, 0 ) <> 0,
            MAX ( Table1[Col1] )
        )
    )
RETURN
    IF (
        HASONEVALUE ( Table1[Col1] ),
        COUNTROWS (
            FILTER ( t, [Col2_] <> BLANK () && [Col2_] = MAX ( Table1[Col1] ) )
        ) + 0,
        COUNTROWS ( FILTER ( t, [Col2_] <> BLANK () ) ) + 0
    )

If my understanding is not correct, please let me know.

BTW, .pbix file attached.

Best Regards,

Icey

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

[amitchandak]

@Anonymous , one of the way is to split the column into rows

refer

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[Anonymous]

You can use the SEARCH function to look for a string inside a string.