cancel
Showing results for
Did you mean:
Frequent Visitor

## Newbie in distress - How to identify distinct

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Newbie in distress - How to identify distinct

@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
)
```
4 REPLIES 4
Super User

## Re: Newbie in distress - How to identify distinct

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

Frequent Visitor

## Re: Newbie in distress - How to identify distinct

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

Many thanks!

Super User

## Re: Newbie in distress - How to identify distinct

@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"```
Super User

## Re: Newbie in distress - How to identify distinct

@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
)
```