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
Dylan73
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

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

Regards
Zubair

Please try my custom visuals

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

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
Frequent Visitor

thanks for the prompt reply!!

 

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

 

 

Many thanks!

@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"

Regards
Zubair

Please try my custom visuals

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

Regards
Zubair

Please try my custom visuals

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.