Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Summarize with distinct values

Hello everyone!

 

I have a table that i created using summarize:

SUMMARIZE('table, 'table'[ID], 'table'[age], 'table'[description])
 
But there is repeated rows because the "age"
For exemple:
IDAgeDescription

1

10aaa
111aaa
312bbb
312bbb

 

I need create a summarize with disticnt values, if its possible, but i dont know to do it.

 

Exemple of output:

IDAgeDescription1
111aaa
312bbb

 

Doesnt matter wich age wil be print (10 or 11), i just need one ID on my table.

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hello @henriquemalone ,

    Create a column as shown below:

    Column = 
var _previousid=CALCULATE(MAX('Table'[ID]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Age]<EARLIER('Table'[Age])))
Return
IF(_previousid=BLANK(),'Table'[Description],BLANK())

    Next, you'll see:

    Screenshot 2020-10-05 162329.png

    For the related .pbix file, see attachment pls.

    Saludos
    Kelly

    Have I answered your question? Mark my position as a solution!

3 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hello @henriquemalone ,

    Create a column as shown below:

    Column = 
var _previousid=CALCULATE(MAX('Table'[ID]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Age]<EARLIER('Table'[Age])))
Return
IF(_previousid=BLANK(),'Table'[Description],BLANK())

    Next, you'll see:

    Screenshot 2020-10-05 162329.png

    For the related .pbix file, see attachment pls.

    Saludos
    Kelly

    Have I answered your question? Mark my position as a solution!

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    Anonymous 

    Are you creating a dimension table for IDs?

    If so, create a new table using VALUES(table[id])
    then create a relationship between this dimension table and your fact table by linking the id fields. Use the dimension Id in your visuals. 
    However, beware that if you include the age column the id = 1 will be repeated since there are 2 age values for this id. If you want to avoid this, create a measure to find the MAX value for age and use that measure instead of the column in the visual

  • negi007's avatar
    negi007
    Community Champion
    5 years ago

    Anonymous  Open your data in power query window and there you can remove duplicate values like below. right click on the ID column and select remove duplicates

     

     

    or directly use below code 

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0ABKJiYlKsTpQAUMkAWOQgBGQSEpKwiIQCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Age = _t, Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Age", Int64.Type}, {"Description", type text}}),
    #"Removed Duplicates1" = Table.Distinct(#"Changed Type", {"ID"})
    in
    #"Removed Duplicates1"