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 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

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

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Vera_33 here is my data model...

  • 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.