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 val...
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.