CODEWITHSUNDEEP
powerbidax
Lanaa
Lanaa

Reputation: 159

DAX Measure count transaction based on condition

I’m trying to create dax measure that counts transactions for only ids with one transaction or more in the same table for specific transaction type. example transactions table (from this table I need to count number of transactions for customers whome having atleast one transaction of type A:

id type count
1 A 10
2 B 5
2 C 11
3 A 4
3 C 1

expected output :

type sum(count)
A 14
B 0
C 1

Noting that I need only measures to achieve this since I’m connecting to SSAS model (live connection)

Upvotes: 1

Views: 2696

Answers (2)

smpa01
smpa01

Reputation: 4282

Possible with a very efficient single measure and from the current table without requiring any table modification.

Measure =
CALCULATE (
    SUM ( 'Table 1'[count] ),
    CALCULATETABLE ( VALUES ( 'Table 1'[id] ), 'Table 1'[type] IN { "A" } )
)

Solution

Upvotes: 3

mkRabbani
mkRabbani

Reputation: 16908

You need 2 Custom Column and 1 Measure to achieve your required output as below-

Custom Columns-

custom_cplumn_count_A = 

var current_row_id = your_table_name[id]
var a_count =
COUNTROWS(
    FILTER(
        ALL(your_table_name),
        your_table_name[type] = "A"
            && your_table_name[id] = current_row_id
    )
)

RETURN if(a_count >= 1,1,0)

custom_column_count_A_2 = your_table_name[count] * your_table_name[custom_cplumn_count_A]

Measure-

sum_count = sum(your_table_name[custom_column_count_A_2])

Here is the final output-

enter image description here

Upvotes: 0

Related Questions