Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Create a Ranking as a Calculated Column

Hello PBI users,

 

I would like to create a calculated column that woud create a ranking as below, (it is a ranking but essentially I would like to show a distinct number based on how many times the customer appeared previously)

 

Below is the table, Enumerate is my desired result, I would like to count for all instances that we the Paid column is not "Yes".

 

CustomerPaid?Enumerate
LegoYes-
LegoNo1
LegoNo2
LegoNo3
StraussYes-
StraussYes-
StraussNo1
A&SNo1
A&SNo2
A&SNo3
A&SNo4
A&SNo5

 

Thanks in advance!

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous ;

You could add a custom column in power query.

1.add custom column:

= Table.Group(#"Removed Columns", {"Customer", "Paid?"}, {{"Count", each  
Table.AddIndexColumn( _, "Index", 1, 1, Int64.Type)}})

2.expand it.

vyalanwumsft_0-1659505828631.png

3.add condition column.

vyalanwumsft_1-1659505844758.png

 

The final show:

vyalanwumsft_2-1659505862268.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8klNz1fSUYpMLQaSukqxOnAhPxBhiCFihCFiDBYJLilKLC0uRjOLkCiSJY4xpQYGRmbB+EWNsIoaYxU1wSpqqhQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Paid?" = _t, Enumerate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Paid?", type text}, {"Enumerate", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Enumerate"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Customer", "Paid?"}, {{"Count", each  
Table.AddIndexColumn( _, "Index", 1, 1, Int64.Type)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Index"}, {"Count.Index"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Count", "Enumerate", each if [#"Paid?"] = "Yes" then "-" else [Count.Index]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Count.Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Enumerate", type text}})
in
    #"Changed Type1"


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous ;

You could add a custom column in power query.

1.add custom column:

= Table.Group(#"Removed Columns", {"Customer", "Paid?"}, {{"Count", each  
Table.AddIndexColumn( _, "Index", 1, 1, Int64.Type)}})

2.expand it.

vyalanwumsft_0-1659505828631.png

3.add condition column.

vyalanwumsft_1-1659505844758.png

 

The final show:

vyalanwumsft_2-1659505862268.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8klNz1fSUYpMLQaSukqxOnAhPxBhiCFihCFiDBYJLilKLC0uRjOLkCiSJY4xpQYGRmbB+EWNsIoaYxU1wSpqqhQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Paid?" = _t, Enumerate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Paid?", type text}, {"Enumerate", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Enumerate"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Customer", "Paid?"}, {{"Count", each  
Table.AddIndexColumn( _, "Index", 1, 1, Int64.Type)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Index"}, {"Count.Index"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Count", "Enumerate", each if [#"Paid?"] = "Yes" then "-" else [Count.Index]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Count.Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Enumerate", type text}})
in
    #"Changed Type1"


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

tamerj1
Super User
Super User

@Anonymous 

As I mentioned earlier you should have either a date or an index column. If you don't then you have to add one using power query. Then try the following code ranking by either date or index (whatever numeric unique per customer column you have)

 

EEnumerate =
IF (
    TableName[Paid?] = "No",
    RANKX (
        CALCULATETABLE (
            TableName,
            ALLEXCEPT ( TableName, TableName[Customer], TableName[Paid?] )
        ),
        TableName[Date]
    )
)

 

Anonymous
Not applicable

Hello Tamerj1, 

 

I do not have a date table neither an index table.

 

By Indextable do you mean just to insert a table with 1-1000 for example and do not create a realationship with the main table?

 

Thank you

@Anonymous 

Yes just insrt an index column using power query. It is just one click operation. Then in dax code replace [Date] with [Index]

tamerj1
Super User
Super User

Hi @Anonymous 

do you have a date or index column?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.