Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How can I create this Measure?

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!

Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

3 Replies

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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