Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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".
Customer | Paid? | Enumerate |
Lego | Yes | - |
Lego | No | 1 |
Lego | No | 2 |
Lego | No | 3 |
Strauss | Yes | - |
Strauss | Yes | - |
Strauss | No | 1 |
A&S | No | 1 |
A&S | No | 2 |
A&S | No | 3 |
A&S | No | 4 |
A&S | No | 5 |
Thanks in advance!
Solved! Go to Solution.
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.
3.add condition column.
The final show:
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.
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.
3.add condition column.
The final show:
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.
@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]
)
)
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]
Hi @Anonymous
do you have a date or index column?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |