How can I create a calculated column based off a different table in PowerBI?

0
votes

I have two hypothetical tables in PowerBI. The first one, called Query1, has various sale transaction numbers, many of them happening on similar dates (also included). So, we have two pertinent in Query1, and they are 'Query1'[transaction_number] and 'Query1'[transaction_date].

Now, suppose I'd like to create another table, called Query2. The first column is set to 'Query1'[transaction_date]. The second column should be the count of transaction numbers associated with that date, with the underlying information taken from Query1.

I've tried the following:

transaction_count = COUNT('Query1'[transaction_number]) but unfortunately, all I got was a column of the total number of transaction numbers regardless of transaction date. I'd like a column with the count of each transaction per that specific day.

Thanks in advance.

1
powerbidax
Would it be enough to use the "group by" command? – heringer
The GROUPBY function is similar to the SUMMARIZE function. However, GROUPBY does not do an implicit CALCULATE for any extension columns that it adds. Bottom line - SUMMARIZE / SUMMARIZECOLUMNS is better. – Krystian Sakowski

1 Answers

1
votes

You have to use SUMMARIZECOLUMNS Function (DAX).

Query2 =
SUMMARIZECOLUMNS (
    'Query1'[transaction_date],
    "transaction_count ", SUMX ( 'Query1', 'Query1'[transaction_number] )
)

In my understanding you will need SUMX inside SUMMARIZECOLUMNS however if you really need to count number of rows just replace SUMX with COUNTX COUNTX Function (DAX).