What would power bi return if there is no value?

[Anonymous]

Hi,

I'm trying to use if conditional function to sort out data. But if there is no value what would power bi return? 

For example, my data table only contains more than 1 quantity. I want to visualize or count number of customer who have not purchased for last 2 years.  In my data customer A purchased 3 years ago, which is the only data in my excel but Customer A did not purchased any product for last 2 years and I want to mark this customer with 1, others who have purchased 1 or more products in last 2 years with 0. 

If there is no actual data that states customer A purchased 0 for last 2 years, would Power bi consider Blank or 0?

If it returns blank then, I might use this formula:

Case 2-1 = if(calculate(sum(product[quantity]),datesinperiod(Calendar[Date],max(Calendar[Date]),-2,year))=BLANK(),1,0)

If it returns 0 then, I might use this one:

Case 2-1 = if(calculate(sum(product[quantity]),datesinperiod(Calendar[Date],max(Calendar[Date]),-2,year))=0,1,0) or

Case 2-1 = if(calculate(sum(product[quantity]),datesinperiod(Calendar[Date],max(Calendar[Date]),-2,year))<1,1,0)

[Pragati11 · Accepted solution]

Hi @Anonymous ,

If you have got value 0 in your data that represents that customer hasn't purchased in last two years; then Power BI will take it as zero.

If the column has empty values or blank values; Power Bi will consider it BLANKS.

So, whatever you feed to Power BI, it will consider that data that way.

Thanks,

Pragati

[amitchandak · Accepted solution]

@Anonymous , power bi would return blank().

To avoid that you can add +0 , but that will make left join and all values will be used

this example of sold in 3 months and not sold in last three month

Rolling 3 Sales = CALCULATE(sum(Retail[Qty]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-3,MONTH))

Rolling 3 Not Sold = if(CALCULATE(sum(Retail[Qty]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-3,MONTH)) +0>0,blank(),1)

[Pragati11 · Accepted solution]

Hi @Anonymous ,

If you have got value 0 in your data that represents that customer hasn't purchased in last two years; then Power BI will take it as zero.

If the column has empty values or blank values; Power Bi will consider it BLANKS.

So, whatever you feed to Power BI, it will consider that data that way.

Thanks,

Pragati

[vanessafvg]

you can use

if(isblank(field),0,(field))