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.
- meepoknang7 years agoFrequent Visitor
I am aware of the DAX, in fact, I used "ISINSCOPE" to determine if I have at top hierarchy or bottom hierarchy. My challenge lies on how I am able to calculate differently within the same measure.
e.g. Top Hierarchy (Customer) - sum( value at product level)
Next Hierarchy (Product ) - product measure
Right now, I have to create two measures,
Measure 1 (Product) = <some calculation formula>
Measure 2 (Customer) - switch (TRUE, isinscope( Product), Measure_1,
sumx( calculatetable( summarize(Table, Product, "value", [Measure_1]), [value]) )I just wonder if there's a better way of doing, so I don't need to create 2 measures.
- Diego_Moreno3 years agoNew Member
Dude you're a legend, thank you so much!