Filter a table by the aggregation of its self

DAX DAX Calculations
1

I am struggling to filter a table by the aggregation of its self

Basically I have one Table which has a list of all payments as well a “zero” payment for the special few who get there subscription for free. The issue I am having is when I am trying to get
who is a “Paid” subscriber on a (by slicer) specific date. I have managed to get a cumulative total of Income and thought I had the count right till I realised I was not taking into account someone who may has a refund.

So from my table (attached)I would like to get the number of paid VIP subscribers on day X and for the same period the Year before

So from my table by the 12th Dec I would expect a count of 2 FOR 2019; AA02 has Paid AA03 Has paid and AA05 Is also counted as he is a special “Is Zero Payer”
and 2 for 2018 AA02 and AA05 as AA03 was refunded

Then for the 16th Dec I would expect 3 for 2019; AA01,AA03 and AA05 and 4 for 2018 AA01,AA02,AA03(paid,refunded,paid again) and AA05

AA04 never gets counted as they are not a VIP

Now in SQL I can Do this easily BY Filtering the table by aggregated sub query of itself (For reference our Financial year starts 1 Nov)

SELECT COUNT(DISTINCT Company) FROM MyTable
WHERE Company NOT IN (SELECT Company FROM MyTable WHERE PayDate Between ‘20191101’ and ‘20191212’ GROUP BY Company HAVING SUM(PayAmount) >0 )
and PayDate Between ‘20191101’ and ‘20191212’
and [Is Vip] = 1

MyTable.xlsx (17.2 KB) Rows 9 to 15 should be 2018 NOT 2019

2

Take a look at this blog from sam. The long and short of it is that you need a dedicated date table (which needs to be marked as such) and use the filters from that date table. Give it shot and see what you can come up with. If there are still issues post them up here!

3

Hi Thanks for the reply, I do have a date table and a load of measures like sales this Year vs Last, Sales versus targets etc that work just fine, I just can’t find a way to sum up and then filter to get a distinct count as the “sum” always comes across with the Key field company in the result so cannot seem to use except or NOT( IN VALUES …) the big hicup is the aggregate

Kindest Regards
Simon

4

Hi; Someone pointed out the obvious and came up with the following which works a treat

CountNumber =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            FILTER ( 'Table', [Is VIP] = 1 ),
            'Table'[Company],
            "Total", SUM ( 'Table'[PayAmount] ),
            "IsZero", MIN ( 'Table'[Is Zero Payer] )
        ),
        [Total] > 0
            || [IsZero] = 0
    )
)
Thank you to all
5

@tsql_simon
glad you found a solution. I’d use ADDColumns to add the columns not within summarize. Summarize produces a row and filter context, which can lead to seemingly incorrect answers. Also, depending on the size of the ‘Table’ it could be quite a performance hit to use summarize and multiple filters. But if it works for your needs, nothing to worry about

6

Hi Nick;

Interesting, I will look in to that and do some performance checks the data set is cira 180k rows so will be worth the time testing the difference, cheers for your input much appreciated.

Kindest Regards
Simon