Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have search through but could not find any solutions to this.
I have several orders in the data and each order consists of item numbers.
E.g.
Order No. Item No.
12345 1
12345 2
12345 3
12345 3
12333 1
12333 2
12333 2
12333 2
In excel, I would concatenate order no. and item no. before using =COUNTIF($A$2:$A2,A2) to find out which are distincts.
Order No. Item No. Distinct
12345 1 1
12345 2 1
12345 3 1
12345 3 2
12333 1 1
12333 2 1
12333 2 2
12333 2 3
I would then filter the "distinct" column and remove all numbers except 1 to find my distinct.
How do i perform this in BI? I tried using Count(distinct) and adding columns with distinct formulas but to no avail.
Some help would be greatly appreciated!
Solved! Go to Solution.
You can also do it with DAX with support of an Index Column.
First add an index column from Query Editor. Then you can use this column
Column = RANKX ( FILTER ( Table1, [Order No.] = EARLIER ( [Order No.] ) && [Item No.] = EARLIER ( [Item No.] ) ), [Index], , ASC, DENSE )
Hi @Dylan73
Create a measure like this and place it in a card visual. Table1 is the table you show:
Measure = COUNTROWS ( SUMMARIZECOLUMNS ( Table1[Order No.], Table1[Item No.] ) )
thanks for the prompt reply!!
Creating the measure at the report or data page? Sorry dont understand whats a card visual.
Many thanks!
With Power Query/Query Editor you can do as follows
Please see the attached file as well
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lEyVIrVQfCMUHjG2HnGxsj6wDwj/LxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order No." = _t, #"Item No." = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order No.", Int64.Type}, {"Item No.", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Order No.", "Item No."}, {{"ALL", each Table.AddIndexColumn(_,"Distinct",1,1), type table}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Item No."}), #"Expanded ALL" = Table.ExpandTableColumn(#"Removed Columns", "ALL", {"Item No.", "Distinct"}, {"Item No.", "Distinct"}) in #"Expanded ALL"
You can also do it with DAX with support of an Index Column.
First add an index column from Query Editor. Then you can use this column
Column = RANKX ( FILTER ( Table1, [Order No.] = EARLIER ( [Order No.] ) && [Item No.] = EARLIER ( [Item No.] ) ), [Index], , ASC, DENSE )
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |