Measure gives unstable results

[Kaatiiaa]

Hi

I have a problem with a measure – it is supposed to calculate number of returning customers based on several conditions. I noticed that it gives wrong results  - some customers that based on the conditions should be not counted as returning were counted.

I thought that I must have done some mistakes in dax code, tried to analyse it but I couldn’t find any problems. Power Bi also does not report any errors.

But now I noticed very strange behaviour of this measure – the results given are unstable even though no changes in the data tables have been done. The number of returning customers reported in card visual changes every time I close and open the Power Bi file! I don’t do any other changes, I am not refreshing source tables, I don’t use/change slicers nor filters, the only thing I do I close and open the file. The result is once, 30075, once 30869, once 30292… and so on

Have you ever encountered such a behaviour? 

My file is quite big – it is 1,3 GB and the main fact table has over 9 million rows. But I haven’t got any problems with this file until this measure.

Please advice. Thank you.

My measure and two other measures it is based on:

MR_RETURNING_CUSTOMERS = 
var _salesthisperiod=[SALES]

var _SellOnlyEP = FILTER(sales_events, sales_events[event_type]="SELL" && sales_events[domain_name] IN {"xxx","yyy","zzz"}) 

var _customers=
ADDCOLUMNS(SUMMARIZE(_SellOnlyEP, sales_events[email_address]),
    "Sales_period",
    [SALES],
    "Sales_running_total",
    [SALES_RUNNING_TOTAL_DATASET_START]
)

var _returningcustomers=
FILTER(_customers,
[Sales_period]<[Sales_running_total] &&
[Sales_period]>0)

return
if(_salesthisperiod>0,
COUNTROWS(_returningcustomers))

SALES = CALCULATE(SUM(sales_events[gross_price]),
FILTER(sales_events, sales_events[domain_name] IN {"xxx","yyy","zzz"} && sales_events[event_type] = "SELL"))

SALES_RUNNING_TOTAL_DATASET_START = 
var CurrentDate = MAX(confirm_date[confirm_date].[Date])

return
CALCULATE([SALES],
FILTER(
    ALL(confirm_date [confirm_date]),
    ISONORAFTER(confirm_date [confirm_date], CurrentDate, DESC)))

[Greg_Deckler]

@Kaatiiaa The only time I have encountered anything like that was when I generate test data using RAND or RANDBETWEEN in a DAX calculated table. Very difficult to troubleshoot your code without understanding the source data.

[Kaatiiaa]

@Greg_Deckler Yes  - it looks like I have been geting RANDom results withou using RAND function 🙂 

A liitle bit more about the data structure: 

The source table (sales_events) is a table with sales events of three types:  sell, cancel and manipulation. For one product_id there is always "sell" row. For product_id's of products that have been returned by the customers there are three rows: sell, cancel and manipulation. In "gross price" column rows "sell" have positive amount, rows "cancel" negative amount (negative value of product price) and rows "manipulation" have positive amount (fraction of product price kept by the seller as the cancellation fee). If you SUM gross_price column with a filter event_type = "sell" you get turnover of all products (even those that have been returned later)

The table has a lot of other columns. Used in this measures are: domain name where the product has been sold, email address of the client (which is the client identifier).

Data source is an oracle database view

confirm_date is the sales date - it is in separate table related to the sales_events table.

[Greg_Deckler]

@Kaatiiaa One other question, this is import mode dataset and not Direct Query, correct?

[Kaatiiaa]

@Greg_Deckler 

  The business logic of this measure should be counting the number of returning customers (within customers buying at specified domains) in a given period (dates will be used on charts or slicers). A returning customer in a given period is understood as the one who bought in that period (e.g. the period selected in the chart) and also bought in the period before that period (from the earliest date in the dataset).

Therefore when I have no specified period selected (meaning I count it for the period containing all dates in my dataset) NONE customers should be treated as returning (as for all [Sales_period]=[Sales_running_total]). But here I get this strange results showing me that I have about 30000 returning customers (once 30075, once 30869, once 30292… and so on). The total number of customers is the database is around 0.9 million.

[Kaatiiaa]

@Greg_Deckler Yes, it is import