Newbie in distress - How to identify distinct

[Dylan73]

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!

[AlB]

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.] ) )

[Dylan73]

thanks for the prompt reply!!

Creating the measure at the report or data page? Sorry dont understand whats a card visual. 

Many thanks!

[Zubair_Muhammad]

@Dylan73

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"

[Zubair_Muhammad · Accepted solution]

@Dylan73

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
)