cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

## Frequency/most repeated value (Dax)

Hi,

I have a two columns are id and status. I am trying to get the most frequency/repeated status based on the two columns.

Example:

Within DM3 has two different status Ok and Not ok and most of them DM3 has Ok therefore the result is Ok. If id has equal status (4 of them is Ok and Not Ok ) then pick any of them Example DM1.

 Id Status Result DM1 OK OK DM1 OK OK DM1 OK OK DM1 OK OK DM1 NOT OK OK DM1 NOT OK OK DM1 NOT OK OK DM1 NOT OK OK DM2 NOT OK NOT OK DM2 NOT OK NOT OK DM2 NOT OK NOT OK DM2 NOT OK NOT OK DM2 NOT OK NOT OK DM3 OK OK DM3 OK OK DM3 OK OK DM3 OK OK DM3 OK OK DM3 OK OK DM3 OK OK DM3 OK OK DM3 NOT OK OK DM3 NOT OK OK DM3 NOT OK OK

1 ACCEPTED SOLUTION
Super User

@Saxon10

``````Result =
VAR __id = [Id]
VAR __t =
MAXX (
TOPN (
1,
SUMMARIZE (
FILTER (Table6,Table6[Id] = __id && Table6[Status] <> BLANK() && Table6[Type] = "MWL" ),
Table6[Status],
"Count", COUNT (Table6[Status] )
),
[Count]
),
Table6[Status]
)
return

IF( Table6[Status] = BLANK() || Table6[Type] <> "MWL" , BLANK() , __t )``````
Did I answer your question? Mark my post as a solution! and hit thumbs up
4 REPLIES 4
Super User

@Saxon10

``````Result =
VAR __id = [Id]
RETURN
MAXX (
TOPN (
1,
SUMMARIZE (
FILTER ( Table1, Table1[Id] = __id ),
Table1[Status],
"Count", COUNT ( Table1[Status] )
),
[Count]
),
Table1[Status]
)
``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Post Prodigy

Can you please advise how can I apply the fillter in your existing formula. I need same output with fillter by type column equal to MWL and not equal to balnks of status column.

 Type Id Status Result MWL DM1 OK OK MWL DM1 OK OK MWL DM1 MWL DM1 MWL DM1 OK OK MWL DM1 OK OK MWL DM1 NOT OK OK MWL DM1 MWL DM1 NOT OK OK MWL DM1 NOT OK OK MWL DM1 NOT OK OK MWL DM2 NOT OK NOT OK MWL DM2 NOT OK NOT OK MWL DM2 NOT OK NOT OK MWL DM2 NOT OK NOT OK MWL DM2 NOT OK NOT OK MWL DM3 OK OK MWL DM3 MWL DM3 OK OK MWL DM3 OK OK MWL DM3 OK OK MWL DM3 NOT OK OK MWL DM3 NOT OK OK MWL DM3 NOT OK OK

Super User

@Saxon10

``````Result =
VAR __id = [Id]
VAR __t =
MAXX (
TOPN (
1,
SUMMARIZE (
FILTER (Table6,Table6[Id] = __id && Table6[Status] <> BLANK() && Table6[Type] = "MWL" ),
Table6[Status],
"Count", COUNT (Table6[Status] )
),
[Count]
),
Table6[Status]
)
return

IF( Table6[Status] = BLANK() || Table6[Type] <> "MWL" , BLANK() , __t )``````
Did I answer your question? Mark my post as a solution! and hit thumbs up
Post Prodigy

Hi,

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.