Forum Discussion

sahooak's avatar
sahooak
Frequent Visitor
9 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
    9 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

amitchandak's avatar
amitchandak
Super User
9 months ago

sahooak , for Fixed LOD refer my video there few thing need to considered 

If the first one a column

Ship Amt CQ Pull In =
IF (
    'Table'[PullInFlag] = "Y"
        && 'Table'[Current Fiscal Quarter] = 0
        && 'Table'[Product] = "Chair",
    'Table'[Ship Amt],
    BLANK()
)


measure like 

Ship Amt CQ Pull In (Filtered Sum) =
VAR _CurrentDay = MAX('Table'[CurrentDayNumber])
RETURN
CALCULATE(SUM('Table'[Ship Amt CQ Pull In]),
    'Table'[Order Day Number] < _CurrentDay
)

 

 

Or a measure like , without column 

Ship Amt CQ Pull In (Filtered Sum) =
VAR _CurrentDay = MAX('Table'[CurrentDayNumber])
RETURN
CALCULATE(
    SUMX(Filter( 'Table',   'Table'[PullInFlag] = "Y"
        && 'Table'[Current Fiscal Quarter] = 0
        && 'Table'[Product] = "Chair")
    'Table'[Ship Amt]),
    'Table'[Order Day Number] < _CurrentDay
)


for LOD in Tableau vs Power BI - FIXED Level of Detail- https://youtu.be/hU-cVOwDCvY

sahooak's avatar
sahooak
Frequent Visitor
9 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
    9 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] )
    )