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 mos...
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 )
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 )
      • Saxon10's avatar
        Saxon10
        Icon for Post Prodigy rankPost Prodigy
        5 years ago

        Hi,

        Thanks for your reply and solution.