Forum Discussion

meepoknang's avatar
meepoknang
Frequent Visitor
7 years ago
Solved

Measure logic for different hierarchy level

  Let's say I have a measure called [Qty]. It has a formula that differs from hierarchy level.   I could define [Qty] to measure the value correctly at product level.   But at the customer...
  • Anonymous's avatar
    Anonymous
    7 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

    AX10
    AY20
    AZ30
    BX40
    BY50
    BZ60
    CX70
    CY80
    CZ100

     

     

    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.