cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper IV

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

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

12 REPLIES 12
Community Support

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.

Helper IV

Hi @v-xiaotang,

Not fixed as spected

Super User

``````Top 10 Counts =
VAR T1 =
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 )``````
Helper IV

Sorry, but it just show to 'count' result

Super User

Helper IV

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

Helper IV

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

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

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.

---

Helper IV

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

Helper IV

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.

Super User

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.

Helper IV

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?

Super User

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