measure to count distinct values from another table

[wini_R]

Hi,

I'm struggling to calculate a measure that returns the number of distinct suppliers for selected material. Here's my simplified model:

I have 2 tables: S -supplier and M - material with 1 to many reliationship (pic. 1)

I'd like to calculate the number of distinct suppliers for each material, i.e. material A is provided by 3 different suppliers, material B is provided by 2 suppliers, material C - 4 suppliers, etc. I'm able to get correct result BUT when doing the calculation based on M table columns (pic 2). The formula is as follows:

count suppliers =

CALCULATE(

    DISTINCTCOUNT(M[Supplier]),

    ALL(M[Supplier])

)

What I need to achieve though, is the same table as in pic 2 but replace M[Supplier] column with S[Supplier] as showed in (pic 3). How to write a count measure so that it works in that table? I've tried with the one below but it returns just total value for all rows:

count suppliers =

VAR _tab =

SUMMARIZE(

    M,

    M[Material],

    S[Supplier],

    "@cnt",

    CALCULATE(

        DISTINCTCOUNT(S[Supplier]),

        ALL(S[Supplier])

    )

)

VAR _result = SUMX(_tab, [@cnt])

RETURN _result

Here are both tables in text format in case anyone needs for testing:

table S

Supplier
1
2
3
4
5
6
7
8

table M

Supplier		Material
1		A
2		D
3		C
4		A
5		B
6		C
7		A
8		C
3		B
4		C

[v-heq-msft · Accepted solution]

Hi @wini_R ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Create measures

Count suppliers = 
CALCULATE(
    DISTINCTCOUNT(M[Supplier]),
    ALLEXCEPT(
        M,
        M[Material]
    )
)

Filter = 
CALCULATE(
    DISTINCTCOUNT(S[Supplier]),
    FILTER(
        M,
        SELECTEDVALUE(M[Material]) = M[Material]
    )
)

Use filter in filter on this visual and set it as 1

Final output

Best regards,

Albert He

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