How can I return a measure value using a DAX query in SSMS?

user_1336632 0
Viewed 191

In my tabular model, I have an issue where data is being duplicated. This is the source of this data:

HV Item Level Amount :=
IF (
    OR (
        MAX ( 'Item Hierarchy'[Depth] ) < [Level of Item Hierarchy],
        MAX ( 'Product Hierarchy'[Level Number] ) < [Level of Product Hierarchy]
    ),
    BLANK (),
    CALCULATE (
        [HV Dollar Amt],
        'R R2M Group',
        'P R2M Group',
        FILTER (
            'Item Hierarchy',
            'Item Hierarchy'[Depth] = MIN ( 'Item Hierarchy'[Depth] )
        )
    )
)

I want to run this on the analysis services so I can see why the numbers are not right.

How do I go about doing this, though? I'm new to DAX and I can see that if I run evaluate 'table_name', it works, but I need to run this whole expression above.

Can someone help me out on this?

dax ssas
1 Answers

EVALUATE always returns a table but you can return a single-cell table like this:

DEFINE
MEASURE 'TableName'[HV Item Level Amount] =
    IF (
        OR (
            MAX ( 'Item Hierarchy'[Depth] ) < [Level of Item Hierarchy],
            MAX ( 'Product Hierarchy'[Level Number] ) < [Level of Product Hierarchy]
        ),
        BLANK (),
        CALCULATE (
            [HV Dollar Amt],
            'R R2M Group',
            'P R2M Group',
            FILTER (
                'Item Hierarchy',
                'Item Hierarchy'[Depth] = MIN ( 'Item Hierarchy'[Depth] )
            )
        )
    )
EVALUATE { 'TableName'[HV Item Level Amount] }

This should return the table

Value
-------
<Measure Output>

Using curly braces {...} around a sequence of commas separated values creates a list, which is equivalent in DAX to a single column table with the generic column name Value.

user_7652260
Related