\n
Current Output Without Vendor Key:
\n\nExpected Output:
\nPartner VendorKey testMeasure\nAbsolutely Pc 4341 1\nAffinitas 4341 1\nBirak 4341 1\nBlue Shield IT Ltd 4316 3\nBlue Shield IT Ltd 4336 3\nBlue Shield IT Ltd 4341 3\n
\nNote: There is already a one to many relationship between the partner table and fact table
\n","author":{"@type":"Person","name":"jcoke"},"upvoteCount":0,"answerCount":1,"acceptedAnswer":{"@type":"Answer","text":"Please test this, and let me know:
\ntestMeasure =\nCALCULATE (\n DISTINCTCOUNT ( fact_table[vendor_key] ),\n ALLEXCEPT ( fact_table, dim_partner[partner_name] )\n)\n
\nAlso, 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!
\ntestMeasure =\nCALCULATE (\n SUMX ( VALUES ( fact_table[vendor_key] ), 1 ),\n ALLEXCEPT ( fact_table, dim_partner[partner_name] )\n)\n
\n","author":{"@type":"Person","name":"Ozan Sen"},"upvoteCount":1}}}Reputation: 1891
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:
Current Output Without Vendor Key:
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
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