Date Table with ALLEXCEPT

DAX DAX Calculations
1

Hello Enterprise DNA community -

hope someone can shed some light on what I may be doing wrong with my calculation below.
Thank you in advance!

Question - How to incorporate Date table dimension when using AllExcept in the fact table?

Intent - Attempting to find weekly transactions per productID.

Table 2 ('Transactions') = many values. (includes FacilityNM,DeviceNM,productID)
Table 1 (Date Table) = one value (using week number column)

WeeklyTransactions = CALCULATE(COUNTROWS('Transactions'),ALLEXCEPT('Transactions',**'Date Table'[Week Number]**,'Transactions'[FacilityNM],'Transactions'[DeviceNM],'Transactions'[productID]))

when i run calculation above, i get following result –
(sorry, I am unable to upload screenshot)

DeviceNM MedID TransactionDTS WeeklyTransactions Week Number
DeviceX 1243 11/1/2018 17:32 8 1
DeviceX 1243 11/1/2018 17:32 1 2
DeviceX 1243 11/1/2018 17:32 5 44
DeviceX 1243 11/1/2018 17:32 1 45
DeviceX 1243 11/1/2018 17:32 1 47
DeviceX 1243 11/1/2018 17:32 10 48
DeviceX 1243 11/1/2018 17:32 8 49
DeviceX 1243 11/1/2018 17:32 3 50
DeviceX 1243 11/1/2018 17:32 6 51
DeviceX 1243 11/1/2018 17:32 6 52
DeviceX 1243 11/1/2018 17:55 8 1
DeviceX 1243 11/1/2018 17:55 1 2
DeviceX 1243 11/1/2018 17:55 5 44
DeviceX 1243 11/1/2018 17:55 1 45
DeviceX 1243 11/1/2018 17:55 1 47
DeviceX 1243 11/1/2018 17:55 10 48
DeviceX 1243 11/1/2018 17:55 8 49
DeviceX 1243 11/1/2018 17:55 3 50
DeviceX 1243 11/1/2018 17:55 6 51
DeviceX 1243 11/1/2018 17:55 6 52

As you can see each week is being repeated for every date here vs. the week the date is in.

2

Hi @vivektrivedi,

Just a couple of questions on this one:

Transaction DTS is the date that the transaction ocurred, right?

Also, I am seeing that the counter for you weekly transactions restarts with a new hour (17:32 and 17:55), I think that the problem is in the context of the calculation.

I think this video will get you an idea on what you can do:

Go through the Solving Analytical Scenarios w/Power & DAX course I am certain there is a count of orders, I don’t remember which video is it at the moment, but I am sure there is one.

Best Regards,

Jorge Galindo

1 Like
3

Jorge -

Thanks for getting back to me on this topic. I believe i have figured it out but still testing and going through “Solving Analytical Scenarios” module to confirm.

Thanks again!

Vivek