Forum Discussion
Measure logic for different hierarchy level
- Anonymous7 years ago
There is a formula in DAX called HASONEVALUE(<columnName>). This will return true if the <columnName> mentioned in the formula returns one single value. I think you can use this in your measure as a boolean condition to determine which formula to use for calculating the result.
For example
Result = IF ( HASONEVALUE(Product), <product-level-formula>, <customer-level-formula> )
This means, at the product level, HASONEVALUE(Product) will return TRUE as a result based on the current product and at the customer level, HASONEVALUE(Product) will return FALSE because there will be multiple products.
For example, consider the following table.
CustomerProductQty
A X 10 A Y 20 A Z 30 B X 40 B Y 50 B Z 60 C X 70 C Y 80 C Z 100 Result = IF ( HASONEVALUE ( Tables[Product] ), SUMX ( Tables, Tables[Qty] / 2 ), SUMX ( Tables, Tables[Qty] ) )This measure will give the following result.
If you notice in the image above, the Result column is calculating differently at Customer Level and Product Level.
At Customer-level it is adding up the Qty field. But at the product level, it is dividing the Qty field.
See if this function can be used in your case.
There is a formula in DAX called HASONEVALUE(<columnName>). This will return true if the <columnName> mentioned in the formula returns one single value. I think you can use this in your measure as a boolean condition to determine which formula to use for calculating the result.
For example
Result = IF ( HASONEVALUE(Product), <product-level-formula>, <customer-level-formula> )
This means, at the product level, HASONEVALUE(Product) will return TRUE as a result based on the current product and at the customer level, HASONEVALUE(Product) will return FALSE because there will be multiple products.
For example, consider the following table.
CustomerProductQty
| A | X | 10 |
| A | Y | 20 |
| A | Z | 30 |
| B | X | 40 |
| B | Y | 50 |
| B | Z | 60 |
| C | X | 70 |
| C | Y | 80 |
| C | Z | 100 |
Result =
IF (
HASONEVALUE ( Tables[Product] ),
SUMX ( Tables, Tables[Qty] / 2 ),
SUMX ( Tables, Tables[Qty] )
)This measure will give the following result.
If you notice in the image above, the Result column is calculating differently at Customer Level and Product Level.
At Customer-level it is adding up the Qty field. But at the product level, it is dividing the Qty field.
See if this function can be used in your case.
Dude you're a legend, thank you so much!