Need help with DAX DIVIDE

[Anonymous]

Hi can someone pls tell me whats wrong with the below formula -

= if(SUM('Install Count Devices'[Installed Device Quantity]) = 0, blank(),DIVIDE([Failure Count],[Installed Device Quantity],0))

When I added this in SSAS Tabular model as a calculated column, I get error as below 

"Installed Device Quantity is not found or cannot be used in this expression". And the error is with the second instance of [Installed Device Quantity] in the formula. Can someone tell me whats wrong with the Divide function or what else.

Thanks

[Anonymous · Accepted solution]

Thanks All for the inputs. I got it resolved by making installed device quantity as a meausre instead of calculated oclumn.

[v-yulgu-msft]

Hi @Anonymous,

Thanks for your sharing. Please kindly mark your shared solution as an answer so that others having similar problem can find the solution more easily.

Regards,
Yuliana Gu

[pawel1]

agree with diblet17.  no need of the last 0

Measure = if(SUM('Install Count Devices'[Installed Device Quantity]) = 0, blank(),DIVIDE([Failure Count],[Installed Device Quantity],0))

[Anonymous]

@mattbrice@Anonymous@Anonymous I guess the problem is with the divide function having numerator from one table and denominator from another table. Is that a valid case to divide one field by another field from two different tables in SSAS.

[Anonymous]

@AnonymousYou could test this theory by declaring a variable and putting the 'other table' value into the variable.  Then use the variable in your divide.

[mattbrice]

Is the column in the table :  'Install Count Devices' ?  The first time you used it you fully qualified the column name, second time you did not.  Try fully qualifying it:  'Install Count Devices'[Installed Device Quantity].  

This is a best practice anyway for code readability. Columns include table name, measures do not.

Also if this is a calculated column, the expression "SUM('Install Count Devices'[Installed Device Quantity])"  will SUM all the values in the entire 'Installed Device Quantity' column in the 'Install Count Devices' table as you have no context transition happening.  CALCULATE needed for that.

[Anonymous]

Hi,

It's my understanding that the divide function handles dividing by zero on it's own so there's no need for the IF(SUM() = 0, BLANK(), DIVIDE()) statement, that could simply be written as:

Divide(Col1, Col2, BLANK())

with the third argument being the output when you divide by zero, which is what the IF() statement was doing.

I don't think this helps with your error however, it seems unrelated. Try simplifying the code as suggest and perhaps it will become more clear?

Thanks,

Will

[Anonymous]

Without knowing your full data structure, is the table in which you are creating this custom column the same table that has the column [Failure Count] and [Installed Device Quantity]?

The way you have referenced them assumes that are on the same data row as the created column as it runs.  Is this actually the case in your tables?

[Anonymous]

@Anonymous The column [Failure Count] is in the same table where I am adding the calculated column. But the column [Installed Device Quantity] is in a different table (table name - Install count devices) and it is a calculated column in that table.  The formula 

Failure % = if('Install Count Devices'[Installed Device Quantity] = 0, blank(),[Failure Count]/[Installed Device Quantity]) worked in Power BI but not working in SSAS. And there is no relationship between the two tables.