Forum Discussion

Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

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.

 

 
IdStatusResult
DM1OKOK
DM1OKOK
DM1OKOK
DM1OKOK
DM1NOT OKOK
DM1NOT OKOK
DM1NOT OKOK
DM1NOT OKOK
DM2NOT OKNOT OK
DM2NOT OKNOT OK
DM2NOT OKNOT OK
DM2NOT OKNOT OK
DM2NOT OKNOT OK
DM3OKOK
DM3OKOK
DM3OKOK
DM3OKOK
DM3OKOK
DM3OKOK
DM3OKOK
DM3OKOK
DM3NOT OKOK
DM3NOT OKOK
DM3NOT OKOK

 

 

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Saxon10 

    Please try this code:

    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 )

4 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Saxon10 
    Add the following column to your table:

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

     

     

    • Saxon10's avatar
      Saxon10
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      Thanks for your reply and help.

      Your formula working well. I need some addtional help and advise.

      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.

       

      TypeIdStatusResult
      MWLDM1OKOK
      MWLDM1OKOK
      MWLDM1  
      MWLDM1  
      MWLDM1OKOK
      MWLDM1OKOK
      MWLDM1NOT OKOK
      MWLDM1  
      MWLDM1NOT OKOK
      MWLDM1NOT OKOK
      MWLDM1NOT OKOK
      MWLDM2NOT OKNOT OK
      MWLDM2NOT OKNOT OK
      MWLDM2NOT OKNOT OK
      MWLDM2NOT OKNOT OK
      MWLDM2NOT OKNOT OK
      MWLDM3OKOK
      MWLDM3  
      MWLDM3OKOK
      MWLDM3OKOK
      MWLDM3OKOK
      MWLDM3NOT OKOK
      MWLDM3NOT OKOK
      MWLDM3NOT OKOK

       

       

       

      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        5 years ago

        Saxon10 

        Please try this code:

        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 )