Forum Discussion

sahooak's avatar
sahooak
Frequent Visitor
8 months ago
Solved

How to replicate a tableau Calculation in Power BI

hello Everyone, I need help with this. I need to replicate one Tableau Calculation into DAX I have this below tableau calucation IF [Order Day Number] < [CurrentDayNumber] THEN { SUM([Ship Am...
Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    8 months ago

    @sahooak , try one of the two

    CALCULATE(
    [Ship Amt CQ Pull In] ,
    allexcept('Data', 'Data'[Product] ), 'Data'[Product] = "Chair"
    )

    or

    CALCULATE(
    sumx(filter('Data'), 'Data'[Product] = "Chair"), [Ship Amt CQ Pull In] ,
    allexcept('Data', 'Data'[Product] )
    )

  • v-dineshya's avatar
    v-dineshya
    8 months ago

    Hi sahooak ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    Please refer below DAX measure.

     

    Ship Amt CQ Pull In LOD =
    VAR FilteredTable =
    FILTER(
    ALL('Data'),
    'Data'[Product] = "Chair"
    && 'Data'[Order Day Number] < 'Data'[CurrentDayNumber]
    )
    RETURN
    CALCULATE(
    [Ship Amt CQ Pull In],
    FilteredTable
    )

     

    Please refer below DAX measure without variables.

     

    Ship Amt CQ Pull In LOD =
    CALCULATE(
    [Ship Amt CQ Pull In],
    ALL('Data'),
    'Data'[Product] = "Chair",
    'Data'[Order Day Number] < 'Data'[CurrentDayNumber]
    )

     

    I hope this information helps. Please do let us know if you have any further queries.

     

    Regards,

    Dinesh

sahooak's avatar
sahooak
Frequent Visitor
8 months ago

Thank you for your reply. Your DAX is not working.
For the Fixed LOD I have created this is DAX which shows the same output like Tableau. BUT how can I add this 

[Order Day Number]<[CurrentDayNumber]
VAR FilteredTable =
    FILTER(
        ALL('Data'),
        'Data'[Product] = "Chair"
    )
VAR TotalShip =
    CALCULATE(
        [Ship Amt CQ Pull In] ,
        FilteredTable
    )
    RETURN
    TotalShip

  

amitchandak's avatar
amitchandak
Super User
8 months ago

sahooak , try one of the two

CALCULATE(
[Ship Amt CQ Pull In] ,
allexcept('Data', 'Data'[Product] ), 'Data'[Product] = "Chair"
)

or

CALCULATE(
sumx(filter('Data'), 'Data'[Product] = "Chair"), [Ship Amt CQ Pull In] ,
allexcept('Data', 'Data'[Product] )
)