cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kaatiiaa
Frequent Visitor

Measure gives unstable results

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

 

 

5 REPLIES 5
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

@Greg_Deckler Yes, it is import

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.