I'm doing an import of a large sales table, and I want to transaction counts which are reliable.

1 year ago 8 Replies
SB
Shaun Brown
5 years ago

I'm hoping this is simple to solve...

I'm doing an import of a large sales table, and I want to transaction counts which are reliable. The problem is that our system gives each transaction a number 6 digits, i.e. a max of 1,000,000 transactions. So it must be recycling them.

How can I count unique transactions for a period, without needing to import the entire dataset, and merge data/transactionID?

I've included a screen grab of the cut down query screen, to give an idea of what's available.

Any help, ideas or whatever, would be much appreciated :-)

2 Likes

Replies

Daníel Hjörvar 5 years ago

In Query Editor, under transform tab, try the "Group by" function.

2 Likes
Corne Van Zyl 5 years ago

Dax filter function

0 Likes
Shaun Brown (5 years ago)

I'm sorry, I'm not sure I understand? I had thought to use a DISTINCTCOUNT function,?

Corne Van Zyl (5 years ago)

Shaun Brown you need to filter the column and for that there is a filter command in DAX. This will allow you to focus on the unique values your looking for. This can be done for a specific date or what ever you would like to focus your calc on

Barny Self 5 years ago

COUNTROWS(SUMMARIZE('TABLE',[transactionid])) Will give you a distinct count of transactions in what I find to be a better way than just distinctcount

0 Likes
Jorge Aguinaga Alvites 5 years ago

Tough call. Looks like you need to count the rows (COUNTROWS()) and that’s it Unless you clearly share what makes a transaction unique

1 Like
Shaun Brown (5 years ago)

Unfortunately not, there are multiple rows for some transactions, where they have more than one product in them... I’ll have a look at a separate query to pull out the date and transactions, and count rows on that. Fingers crossed!!

Daníel Hjörvar (5 years ago)

Shaun Brown since you don't want to load the whole thing to your model, use the group by function in the query editor.

You can "group by" as many columns as you like if you use the advance feauture in the UI but aggregate (sum) the "actualvalue" column. That should create a distinct valued "transactionno" column. Then load the table to your model and then a simple countrows(transactionno) should be enough.

Other solutions require you to load duplicate values of transactionno's into your model and if I understand your problem correctly, that's something you don't wanna do.... unless I'm completely missing the point here