How to replicate a tableau Calculation in Power BI

[sahooak]

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

[amitchandak · Accepted solution]

@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 · Accepted solution]

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]

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]

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

[amitchandak · Accepted solution]

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

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]

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]

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

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]

@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] )
)