Forum Discussion

PowerBI123456's avatar
PowerBI123456
Post Partisan
6 years ago
Solved

Matching by Index

Hi, below is my example. I want to be able to indicate (in a measure) that Drug Unit, Index 2 had an NDC added for the same index 2. I also want to see instances where that is not happening. For example, there is a drug unit for index 3, but there is no NDC added for index 3. Hope that makes sense. Thanks!!

 
 
 
 

Need Help
Tips and Tricks
  • Icey's avatar
    Icey
    6 years ago

    Hi PowerBI123456 ,

     

    Please check if this is what you want:

    Measure =
IF (
    MAX ( 'Table'[Field] ) = "Drug Unit",
    IF (
        COUNTX (
            FILTER ( ALL ( 'Table' ), [index] = MAX ( [Index] ) && [Field] = "NDC" ),
            [Data]
        ) > 0,
        "Yes",
        "No"
    )
)

     

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    PowerBI123456 ,

    if(countx(filter(Table, [index] =earlier([Index]) && [Field] = "Drug Unit" && earlier([field]) ="NDC"),[Data])>0, "Yes", "No")

    or

    if(countx(filter(Table, [index] =earlier([Index]) && [Field] = "Drug Unit" && earlier([field]) ="NDC"),earlier([Data]))>0, "Yes", "No")

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi PowerBI123456 ,

     

    Please check if this is what you want:

    Measure =
IF (
    MAX ( 'Table'[Field] ) = "Drug Unit",
    IF (
        COUNTX (
            FILTER ( ALL ( 'Table' ), [index] = MAX ( [Index] ) && [Field] = "NDC" ),
            [Data]
        ) > 0,
        "Yes",
        "No"
    )
)

     

    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.