Hari
Hari

Reputation: 333

Power BI(DAX)- Create measure to count distinct rows filtered by condition

I have below Customer Transactions data. In a month, customer may buy one or multiple times data pack ,or may not buy data pack.

Irrespective of how many times data purchased in a month by a customer. I'm trying to find number of months that each customer purchased the data. enter image description here

Total Subscribed Months is expected column.

Since customer may buy Data Subscribed (GB) more than once in a month. First I'm calculating Total Data Purchased in a month by customer.

Total Data Purchased = CALCULATE(
                    SUM('Customer Transactions'[Data Subscribed (GB)]),
                        ALLEXCEPT('Customer Transactions','Customer Transactions'[Account Number],'Customer Transactions'[Date])
    )

Second, Calculate number of months customer purchased the data pack.

Total Subscribed Months = 
CALCULATE(
            DISTINCTCOUNT('Customer Transactions'[Account Number] ),
            'Customer Transactions'[Total Data Purchased]>0
            )

But its not working. Please advise how to correct formulae ?

Upvotes: 0

Views: 7914

Answers (1)

Angelo Canepa
Angelo Canepa

Reputation: 1791

Assuming your table looks like this:

Account Number CUSTOMER_TYPE Data Subscribed(GB) Free Data Date Total Subscribed Months
10001 Retail 250 0 01 October 2019 1
10001 Retail 0 100 01 November 2019 1
10002 Retail 200 0 01 October 2019 1
10002 Retail 250 0 01 November 2019 2
10003 Retail 300 0 01 October 2019 2
10003 Retail 0 0 01 October 2019 2
10003 Retail 100 0 01 October 2019 2
10003 Retail 100 0 01 November 2019 2
10003 Retail 100 0 01 November 2019 2
10003 Retail 0 0 01 December 2019 2

DAX Calculations

Total Subscribed Months

Total Subscribed Months = 
CALCULATE (
    DISTINCTCOUNT ( 'Customer Transactions'[Date] ),
    FILTER ( ALL ( 'Customer Transactions'[Data Subscribed(GB)] ), [Data Subscribed(GB)] > 0 )
)

Total Data Purchased

Total Data Purchased = 
    SUM('Customer Transactions'[Data Subscribed(GB)])

Output

Table visual with Account Number, Total Subscribed Months and Total Data Purchased.

enter image description here

Upvotes: 3

Related Questions