Count number of Records in a Dimension Table NOT in a Fact Table

[Anonymous]

I have a fact table named Onbaording Report that contains multiple records for suppliers that have sent Purchase Orders and Invoiced on those Purchase Orders.

I have a Dimension table of Suppliers named Supplier Lookup that contains suppliers that Are AND ARE NOT present in Onboarding Report.

I have a One to Many Relationship present between the two tables as shown below.

I Created the below measure to count the number of suppliers in scope from the Supplier Lookup table.

Number Suppliers In Scope = calculate(DISTINCTCOUNT('Supplier Lookup'[SupplierKey]))

I have written the below measure to count the suppliers present in Suppier Lookup and NOT in Onboarding Report.

Number Suppliers Not Sent POs = coalesce(

CALCULATE([Number Suppliers In Scope],

ISBLANK('Onboarding Report'[SupplierKey]),

CROSSFILTER('Onboarding Report'[Supplier Number],'Supplier Lookup'[New Vendor ID ],Both)

),0)

I am getting 0 for the results and don't understand why.

Mark Janecek

PTP Metrics Analyst

Johnson Controls

[jdbuchanan71]

@Anonymous 

Could we go at it the other way.  Calc the whole count, the count of those present then the difference is the count of those missing?

Number Suppliers Not Sent POs =
VAR _AllSuppliers =
    CALCULATE ( DISTINCTCOUNT ( 'Supplier Lookup'[SupplierKey] ) )
VAR _PresentSuppliers =
    CALCULATE (
        DISTINCTCOUNT ( 'Supplier Lookup'[SupplierKey] ),
        'Onboarding Report'
    )
VAR _MissingSuppliers = _AllSuppliers - _PresentSuppliers
RETURN
    _MissingSuppliers