jcoke
jcoke

Reputation: 1891

Wrong count total from calculated measure

I am trying to get a distinct count of the vendor keys for each partner but for some reason every time I bring in the vendorkey column from the fact table it returns 1 for measures, when I remove the vendorkey column it returns the accurate count. Is there something I'm missing?

DAX:

testMeasure = CALCULATE(DISTINCTCOUNT(fact_table[vendor_key]),ALLEXCEPT(dim_partner,dim_partner[partner_name]))

Current Output With Vendor Key:

enter image description here

Current Output Without Vendor Key:

enter image description here

Expected Output:

Partner             VendorKey testMeasure
Absolutely Pc         4341          1
Affinitas             4341          1
Birak                 4341          1
Blue Shield IT Ltd    4316          3
Blue Shield IT Ltd    4336          3
Blue Shield IT Ltd    4341          3

Note: There is already a one to many relationship between the partner table and fact table

Upvotes: 0

Views: 177

Answers (1)

Ozan Sen
Ozan Sen

Reputation: 2615

Please test this, and let me know:

testMeasure =
CALCULATE (
    DISTINCTCOUNT ( fact_table[vendor_key] ),
    ALLEXCEPT ( fact_table, dim_partner[partner_name] )
)

Also, DistincCount() is a problematic function, and causes you lots of trouble with your measure if your data grows. change your measure with this. Preference is up to you!

testMeasure =
CALCULATE (
    SUMX ( VALUES ( fact_table[vendor_key] ), 1 ),
    ALLEXCEPT ( fact_table, dim_partner[partner_name] )
)

Upvotes: 1

Related Questions