TREAT AS DAX Error - Returning back Zero Values

[Anonymous]

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 have Y Curve % values in my FACT Table.

FACT Table = Input Table

Satndard Alone Table = 'Y Curves

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

[BB] is the Product in the Input table

[v-cazheng-msft]

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.

[Vera_33 · Accepted solution]

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

[Anonymous]

Vera_33 here is my data model...