Help to convert M language to DAX to count disctinct, filter top 10 and reorder

[jr3151006]

Hi,

we have a simple table with to many descriptions, so we need group them, count distinct, select descending count, filter the top 10 and then reorder based on the 'description' field.

Today, to accomplish that, we create a new referenced table to the 'table01' and then we done all things as below. And it's shown using a pie chart on a page.

>>> How to conver it a DAX measure??

Any help is appreciated

---

Current referenced table:

let
    Source = #"Table01",
    #"Grouped Rows" = Table.Group(Source, {"Description"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Count", Order.Descending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",10),
    #"Sorted Rows1" = Table.Sort(#"Kept First Rows",{{"Description", Order.Ascending}})
in
    #"Sorted Rows1"

[v-xiaotang]

Hi @jr3151006 

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[jr3151006]

Hi @v-xiaotang,

Not fixed as spected

[tamerj1]

Hi @jr3151006 

Please use

Top 10 Counts =
VAR T1 =
    ADDCOLUMNS (
        ALLSELECTED ( TableName[Description] ),
        "@Count", CALCULATE ( COUNTROWS ( TableName ) )
    )
VAR T2 =
    TOPN ( 10, T1, [@Count] )
VAR MinCount =
    MINX ( T2, [@Count] )
VAR CurrentCount =
    COUNTROWS ( TableName )
RETURN
    IF ( CurrentCount >= MinCount, CurrentCount )

[jr3151006]

Sorry, but it just show to 'count' result

[tamerj1]

@jr3151006 

please share a screenshot

[jr3151006]

Hi tamerj1,

for sure, I´ll adjusting my 'test.pbix' to get screen shots.

As you can see in the table below, each person has a preferred color. Let's say that table has too much records, but we need to know what is the 'TOP 3' preferred colors.

How to accomplish that in the same table "Table" - in order to respect the 'DateCommom' when filtering by year - for example, using a 'slicer'?

In that case, would expected that the pie chart to show 'BLUE' with '3' records, 'Yellow' with '2' records and the thirdone could be anyone (red or Brown or Pink) based on sort descending (based on year or year+month on a slicer).

[jr3151006]

>>> I´m almost 'there' <<<

-------------------------------

After read another post (link below), I did:

1) Edited the source table in order to add a 'new custom column' named 'Count' and set its value as '1';

2) Add a new 'pie chart' to the page and then select first the 'PreferredColor' column for 'legend' and also for 'Values' - where I set up it to 'count'

Take a look on the image below. 

* Right now, I´ll research on how to 'top n' only few of them.

---

Usefull link: https://community.powerbi.com/t5/Desktop/summarize-row-counts-in-a-new-column/m-p/13339

[jr3151006]

Right now, I realize that I don't need an extra column as 'count'.
🙄

[jr3151006]

I found a usefull way to 'count' the 'duplicated' itens but I cannot go forward to calc as a new column or use it as a measure.

https://www.youtube.com/watch?v=bE0W8SkOS4A

[tamerj1]

@jr3151006 

If you wish, we can connect tomorrow or day after tomorrow to look into you problem. Please let me know yoyr time zone and your time preference. 

[jr3151006]

Hi @tamerj1,

1) I really appreciate your help and attention. My time zone is '-3' for Brazil and I speak english and use MS TEAMS here on our M365 tenant - if you can.

2) I found 03 ways to find/count repeated records using this video (PT-BR channel): https://www.youtube.com/watch?v=Af7b5wZBlKQ&t=484s

3) I'm able to find repeated ones - using a 'Measure' that just count records. That knownledge was obtained from vídeo cited on 'item 2' above. I just use it with the 'PreferredColor' column.

4) I found a way to filter 'top n', using the PBI desktop 'filters' pane, after watch this video (PT-BR): https://youtu.be/ZXRC0p1mrZc?t=506.

>>> 'TOP N' misson accomplished (?).

----------------------------

My concern right now is to rely on the 'filters pane' and then the end user modifies it and loses all work until now. So, I would like a 'DAX' help to use 'TOP N' instead to use 'filters pane'.

Do you think that it's possible?

[tamerj1]

Hi @jr3151006 
Sorry for the late reply. We can connect 8:00pm Dubai time (1:00pm Brasilia time). Here is my teams email address:

tamer@tisunenergy.com