none
DAX incompatible with Tabular model, but working in Power BI

    Question

  • I have created a small tabular model to convert my fact measures in diff currency to USD.

    4 tables are -

    1. FactAmount (fact table, measures)

    2. Exchange Rate (fact table, conversion value from different currency to USD and viceversa)

    3. Currency (Dimension, distinct currency values like USD, INR ..)

    4. Date (Dimension, date values)

    I created relationship between facts and dim.

    FactAmount is joined with Date and Currency and similarly ExchangeRate is joined with Date and Currency.

    I tried below formula to convert measures  to USD-

    RatetoUSD:= 

    var CurrentDate = RelatedTable('Date')

    var CurrentCurrency = Distinct('FactAmount' [Currency])

    Return

    LookupValue(

    ExchangeRate[AverageRate],

    ExchangeRate[CurrencyName], Distinct('FactAmount' [Currency]),

    ExchangeRate [Date], Calculate (MAX('ExchangeRate'[Date]), 'ExchangeRate' <= CurrentDate,

    ExchangeRate[CurrencyName] = CurrenctCurrency,

    ALL(ExchangeRate)

    )

    )

    Issue is value of var CurrentDate should be (it should use Related and not RelatedTable)

    Var CurrentDate = Related('Date'[Date])

    But Related is throwing error in Tabular. But Related function is working in PowerBI and its giving desired results.

    please suggest


    • Edited by SMV123 Friday, July 13, 2018 12:53 PM
    Friday, July 13, 2018 12:52 PM

All replies

  • Probably you created a calculated column in Power BI and now you are creating a measure in Tabular.

    RELATED works in both, but you need a row context and you don't have one in a measure.


    Marco Russo http://www.sqlbi.com


    Saturday, July 14, 2018 7:50 AM
  • Hi SMV123,

    Thanks for your question.

    >>>But Related is throwing error in Tabular. But Related function is working in Power BI and its giving desired results.
    Just like Marco said RELATED works in both Power BI and Tabular. In your scenario, you can try to create a calculated column in your SSAS tabular model, see if it works for you. If not, please provided the detailed error messages here, so that we can help you in more efficient way. Thanks for your support and understanding.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by SMV123 Monday, July 16, 2018 1:59 PM
    • Unmarked as answer by SMV123 Monday, July 16, 2018 1:59 PM
    Monday, July 16, 2018 1:20 AM
    Moderator
  • Hi

    I am using RateToUSD as calculated column.

    Unable to upload error screenshot , any other way i can share error screen?

    • Marked as answer by SMV123 Monday, July 16, 2018 6:14 PM
    • Unmarked as answer by SMV123 Monday, July 16, 2018 6:14 PM
    • Edited by SMV123 Monday, July 16, 2018 6:15 PM
    Monday, July 16, 2018 2:00 PM
  • Unable to upload error screenshot , any other way i can share error screen?

    We don't really need a screen shot, just the text of the error message should be enough

    http://darren.gosbell.com - please mark correct answers

    Monday, July 16, 2018 9:14 PM
    Moderator
  • This is formula 

    RatetoUSD:=  
    var CurrentDate = Related ('Date'[Date])
    var CurrentCurrency = 'FactAmount'[Insured Currency]
    Return
    lookupvalue (
    'Exchange Rate'[Average Rate],
    'Exchange Rate'[Currency Name], 'FactAmount'[Insured Currency],
    'Exchsnge Rate'[Date], Calculate (
    max('Exchange Rate'[Date]),
    'Exchange Rate'[Date]<= CurrentDate,
    'Exchange Rate'[CurrencyName] = CurrentCurrency,
    All('Exchange Rate')
    )
    )

    This is the error -

    'The following syntax error occurred during parsing: Invalid token, Line 6, offset 1,

    and in above formula , Related('Date'[Date]) is red.

    var CurrentDate = Related ('Date'[Date])

    Tuesday, July 17, 2018 12:06 PM
  • Hi SMV123,

    Thanks for your response.

    According to your formula, please first create a calculated column called CurrentDate as Related ('Date'[Date]). Then you can use calculated column CurrentDate to create you calculated column RateToUSD.

    RatetoUSD :=
    VAR CurrentCurrency = 'FactAmount'[Insured Currency]
    RETURN
        LOOKUPVALUE (
            'Exchange Rate'[Average Rate],
            'Exchange Rate'[Currency Name], 'FactAmount'[Insured Currency],
            'Exchsnge Rate'[Date], CALCULATE (
                MAX ( 'Exchange Rate'[Date] ),
                'Exchange Rate'[Date] <= EARLIER ( 'Exchange Rate'[CurrentDate] ),
                'Exchange Rate'[CurrencyName] = CurrentCurrency,
                ALL ( 'Exchange Rate' )
            )
        )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 18, 2018 7:12 AM
    Moderator
  • Im having the same error:

    The following syntax error ocurred during parsing: invalid token, lin4, offset 39, .

    When creating a calculated column on factual :

    Sorting XXX =

    IF (ISBLANK(RELATED('Event Generators'[XXX Level 2])) 
    ;"Not assigned" 
    ;RELATED('Event Generators'[XXX Level 2])  

       )

    I think it is exactly the same error and i'm not totally sure why it is happening, it seems at least very inefficient to have to copy a column content from a dimension to a factual table  one million times, when RELATED gives me a relation from n to 1. Is it exactly the same error and i must copy the content of the column to this table?

    What am i missing here?


    • Edited by Lrmmf_ Friday, August 31, 2018 5:51 PM
    Friday, August 31, 2018 5:39 PM