CODEWITHSUNDEEP
powerbidax
daOnlyBG
daOnlyBG

Reputation: 601

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

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.

Upvotes: 0

Views: 2660

Answers (1)

Krystian Sakowski
Krystian Sakowski

Reputation: 1653

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).

Upvotes: 1

Related Questions