Dax Calculation 1st and 2nd Best Amount - Part 2 (Performance)

@jafernandezpuga - Hope you doing well , so there was a calculation that you helped me with 1st best weight and 2nd best weight. That is working seamlessly with less data thanks for that , however i am noticing performance issue if i have around half a million records , can we have the 1st best weight & 2nd best weight as calculated column ?
Attaching the pbix file u had shared with calcs
MVNO Test (2)_JAFP.pbix (114.4 KB)

It would be of great help if you can provide ur inputs , when i am trying to create the 2nd best weight as column it gives circular dependency

Hi @Vishy,
The measure you want to implement as a calculated column of Current 2nd Best Weight is this?

Current 2nd Best Weight S1 =

VAR _S1withBestWeight =

ADDCOLUMNS(

    ALL( S1 ),

    "BestWeight", [Current Best Weight S1]

)

VAR_CurrentBestWeight = [CurrentBestWeightS1]

VAR _NextBestDate = CALCULATE(

    MIN(Dates[Date] ),

    FILTER(

        _S1withBestWeight,

        [BestWeight] > _CurrentBestWeight

    )

)

VAR _NextBestDate2 = IF(

    ISBLANK( _NextBestDate ),

    CALCULATE( MAX( Dates[Date] ), ALL( Dates[Date] ) ),

    _NextBestDate

)

VAR _VirtualTable = FILTER(

    _S1withBestWeight,

    [BestWeight] <= _CurrentBestWeight &&

    [Date] < _NextBestDate2

)

VAR _Result = MINX(

    TOPN( 2, _VirtualTable, [Total Weight] ),

    [Total Weight]

)

RETURN

In pbix it appears like this:

Current 2nd Best Weight S1 =
VAR _CurrentDate = SELECTEDVALUE( Dates[Date] )
VAR _VirtualTable = FILTER(
    ALL( S1 ),
    S1 [Date] <= _CurrentDate
)
VAR _Result = MINX(
    TOPN( 2, _VirtualTable, [Total Weight] ),
    [Total Weight]
)

RETURN
IF( [Total Weight S1], _Result)

Do you need to do the calculated column in DAX or could we implement it in Power Query?

Regards

1 Like

@jafernandezpuga - Yes i want the below measure -
Current 2nd Best Weight S1 =

VAR _CurrentDate = SELECTEDVALUE( Dates[Date] )

VAR _VirtualTable = FILTER(

ALL( S1 ),

S1 [Date] <= _CurrentDate

)

VAR _Result = MINX(

TOPN( 2, _VirtualTable, [Total Weight] ),

[Total Weight]

)

RETURN

IF( [Total Weight S1], _Result)

to be moved as calculated column or power query anything is fine with me , which ever would be more performant

1 Like

Hi @Vishy,
I have created the columns you need in Power Query. I have applied it on the S1_New table that I have obtained by exporting the data from S1 to CSV with DAX Studio.

let
  Source = Csv.Document(
    File.Contents(
      "C:\Enterprise DNA\Forum\Dax Calculation 1st and 2nd Best Amount - Part 2 (Performance)\S1.csv"
    ),
    [Delimiter = ";", Columns = 17, Encoding = 1252, QuoteStyle = QuoteStyle.None]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {
      {"SUBSCRIPTION_ID", type text},
      {"Total Weight", type number},
      {"Date", type text},
      {"2nd Best Weight", type number},
      {"Current Best Weight", type number},
      {"Previous index", type number},
      {"Current Index", type number},
      {"Avg Index", type number},
      {"Output PM", type number},
      {"Output Var %", type number},
      {"_Previous_Best_100", type number},
      {"Previous more than", type number},
      {"Sum of Data_Wholesale", type number},
      {"Sum of Voice_Incoming_Wholesale", type number},
      {"Sum of Voice_Outgoing_Wholesale", type number},
      {"Sum of Voucher_Spend", type number},
      {"Sum of Data_Free_Used", type number}
    }
  ),
  #"Removed Other Columns" = Table.SelectColumns(
    #"Changed Type",
    {"SUBSCRIPTION_ID", "Total Weight", "Date"}
  ),
  #"Added Custom Column" = Table.AddColumn(
    #"Removed Other Columns",
    "Date_New",
    each Text.Combine(
      {Text.Middle([Date], 8, 2), "/", Text.Middle([Date], 5, 2), "/", Text.Start([Date], 4)}
    ),
    type text
  ),
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom Column", {"Date"}),
  #"Renamed Columns" = Table.RenameColumns(#"Removed Columns", {{"Date_New", "Date"}}),
  #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}),
  #"Added Best Weight" = Table.AddColumn(
    #"Changed Type1",
    "Best Weight",
    each List.Max(Table.SelectRows(#"Changed Type1", (a) => a[Date] <= [Date])[Total Weight]),
    type number
  ),
  #"Added Best 2nd Weight" = Table.AddColumn(
    #"Added Best Weight",
    "Best 2nd Weight",
    each
      if [Date] = List.Min(#"Added Best Weight"[Date]) then
        [Best Weight]
      else
        List.Max(
          Table.SelectRows(
            #"Added Best Weight",
            (a) => a[Date] <= [Date] and a[Total Weight] < [Best Weight]
          )[Total Weight]
        ),
    type number
  )
in
  #"Added Best 2nd Weight"

Regards
S1.csv (2.2 KB)
MVNO Test (2)_JAFP.pbix (121.2 KB)

2 Likes

This works perfectly fine thanks so much , so i have further requirement on this now would create a new post . Thanks so much for help till now

policy is not to address to one person in the forum

@Keith - Thanks for highlighting it !!