Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

TREAT AS DAX Error - Returning back Zero Values

Hi Experts   I have the following measure where i am trying to populate the % value from the Y Curves Table which has no relationship to the FACT Table into a  Matrix Table - Currently i do not hav...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

     

    I tried to use DAX Formatter to format your DAX code and understand your logic, but failed...it looks like you have filtered the stand alone table to [Product] = "BB" && [Curve Ref] = "Blah' , but you try to pass the values in fact table [Y-Yield Curve] and [BB]? Where is your SELECTEDVALUE from your slicer?

     

    Maybe it is better to provide some sample data and expected result.

     

    Interset =
VAR _Int =
    SELECTEDVALUE ( input[BB] ) // i have a Slicer from which i select the product...
VAR Result =
    CALCULATE (
        AVERAGEX ( 'Y Curves', 'Y Curves'[Y-Percentages] ),
        'Y Curves'[Product] = "BB"
            && 'Y Curves'[Curve Ref] = "Blah",
        TREATAS ( VALUES ( Input[Y-Yield Curve] ), Input[BB] ),
        'Y Curves'[Curves Ref],
        'Y Curves'[Product]
    )
RETURN
    Result

     

v-cazheng-msft's avatar
v-cazheng-msft
Community Support
5 years ago

Hi Anonymous 

TREATAS　function will apply the result of a table expression to filter an unrelated table. For more details about the usage of TREATAS, you can refer Building a Virtual Relationship in Power BI – Basics of TREATAS DAX Function. ‘Input’[Y-Yield] and ‘Input’[BB] are all the columns in ‘Input’ table, ‘Input’[Y-Yield] columns will be filtered automatically when ‘Input’[BB] selected in Slicer. Therefore, I make some changes to your formula.

 

Interset =

VAR _Int =

    SELECTEDVALUE ( input[BB] ) //how you want to use the current value in Slicer

VAR Result =

    CALCULATE (

        AVERAGEX ( 'Y Curves', 'Y Curves'[Y-Percentages] ),

        TREATAS ( VALUES ( 'Y Curves'[Product] ), 'Input'[BB] ),

        'Y Curves'[Product] = "BB"

            && 'Y Curves'[Curve Ref] = "Blah"

    )

RETURN

Result

 

If it doesn't work, please share us some sample data without sensitive data and describe your needs more clearly.

 

Best Regards

Caiyun Zheng

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.