Generate a rank column with dates for related table

[Friedrich]

Hello to everyone.

I want to create a column where the dates of the related table are shown in ranked ascending order. I want to do it with a measure, because of further slicer use. I mention a unrelated rank table (1, 2, 3 ...) where the dates are filtered and are shown ranked ascending.

Thanks for the help.

Best regards

[Fowmy · Accepted solution]

@Friedrich 

You can do in Power Query.

This is my source data which is similar to your Table 2.

The output you are expecting:

Paste this code in a blank query, go to the advanced editor, clear everything, and paste. You can check the steps I performed to so the transformation.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AUiIwOlWB2ogBG6gDGSgBG6FpCAoQGSgDFEhW9iJYJrBuHGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store ID" = _t, Dates = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dates", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Store ID"}, {{"Count", each _, type table [Store ID=nullable text, Dates=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Dates]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Custom.1", "1"}, {"Custom.2", "2"}, {"Custom.3", "3"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Count"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"1", type date}, {"2", type date}, {"3", type date}})
in
    #"Changed Type1"

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[andre · Accepted solution]

You are basically trying to PIVOT your date column based on ranking... here is a video that will walk you through how to do it.  It has a very similar table dsign as what you used in your example:

if you want to see the calculations, you can download the Power BI Desktop file here:

https://businessintelligist.com/2020/07/27/power-bi-dax-tutorial-how-to-pivot-data-using-dax-using-r...

[Friedrich · Accepted solution]

Hello,

i solved it this way.

Best regards 

tables

power pivot table

measure dates

measure datediff

[andre · Accepted solution]

You are basically trying to PIVOT your date column based on ranking... here is a video that will walk you through how to do it.  It has a very similar table dsign as what you used in your example:

if you want to see the calculations, you can download the Power BI Desktop file here:

https://businessintelligist.com/2020/07/27/power-bi-dax-tutorial-how-to-pivot-data-using-dax-using-r...

[Fowmy · Accepted solution]

@Friedrich 

You can do in Power Query.

This is my source data which is similar to your Table 2.

The output you are expecting:

Paste this code in a blank query, go to the advanced editor, clear everything, and paste. You can check the steps I performed to so the transformation.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AUiIwOlWB2ogBG6gDGSgBG6FpCAoQGSgDFEhW9iJYJrBuHGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store ID" = _t, Dates = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dates", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Store ID"}, {{"Count", each _, type table [Store ID=nullable text, Dates=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Dates]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Custom.1", "1"}, {"Custom.2", "2"}, {"Custom.3", "3"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Count"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"1", type date}, {"2", type date}, {"3", type date}})
in
    #"Changed Type1"

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn