DAX formula help self join

[ppgandhi11]

Hi,

I am trying to obtain below recordset using DAX formulas. How would get to it?

Logic:

Provider 1 is associated with Specialty A. So all the providers associated with A becomes RelatedProviders for 1 (1,2,3).

Provider 2 is associated with Specialty A and B. So all the providers associated with A and B becomes RelatedProviders for 2 (1,2,3,4).

Provider 3 is associated with Specialty A. So all the providers associated with A becomes RelatedProviders for 3 (1,2,3).

Provider 4 is associated with Specialty B. So all the providers associated with B becomes RelatedProviders for 4 (2,4).

Thanks.

[LivioLanzo · Accepted solution]

Does this do what you want?

=
GENERATE (
    VALUES ( Providers[Provider] ),
    VAR Specialties =
        CALCULATETABLE ( VALUES ( Providers[Specialty] ) )
    RETURN
        SELECTCOLUMNS (
            CALCULATETABLE (
                VALUES ( Providers[Provider] ),
                TREATAS ( Specialties, Providers[Specialty] ),
                ALL ( Providers[Provider] )
            ),
            "RelatedProviders", [Provider]
        )
)