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 Amt CQ Pull In])}
         END

Ship Amt CQ Pull In=
IF [PullInFlag] = 'Y'
AND [Current Fiscal Quarter] = 0
AND [Product]="Chair"
THEN [Ship Amt] END

For the Fixed LOD Part I have created this below measure in power BI-which is giving exact output like tableau. But I dont know how I can implement this condition [Order Day Number] < [CurrentDayNumber] in my DAX

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

 

 

Thank you,
AS

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

9 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper 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
        Icon for Super User rankSuper 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] )
        )

  • MattiaFratello's avatar
    MattiaFratello
    Icon for Super User rankSuper User
    9 months ago
    TotalShip_Chair_CQ_PullIn = 
CALCULATE(
    SUM('Data'[Ship Amt]),
    'Data'[Product] = "Chair",
    'Data'[PullInFlag] = "Y",
    'Data'[Current Fiscal Quarter] = 0,
    FILTER(
        ALL('Data'),
        'Data'[Order Day Number] < 'Data'[CurrentDayNumber]
    )
)

     

    Try the above.

     

    If this helps, please mark it as a solution and give kudos 👍

    • sahooak's avatar
      sahooak
      Frequent Visitor
      9 months ago

      Thank you for you reply. Your DAX is giving same values in each row.

      this condition should work first

      [Order Day Number] < [CurrentDayNumber] then that Fixed LOD Part.
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper 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] )
    )

  • v-dineshya's avatar
    v-dineshya
    Icon for Community Support rankCommunity Support
    9 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

    • v-dineshya's avatar
      v-dineshya
      Icon for Community Support rankCommunity Support
      8 months ago

      Hi sahooak ,

      We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

       

      Regards,

      Dinesh

      • v-dineshya's avatar
        v-dineshya
        Icon for Community Support rankCommunity Support
        8 months ago

        Hi @sahooak ,

        We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

         

        Regards,

        Dinesh