Forum Discussion

hosea_chumba's avatar
hosea_chumba
Helper I
2 years ago
Solved

DAX Formula

DAX issue
Wednesday

I have a table "Sales"with the columns for product, customer number and group ID. I would like to identify groups that have more than 5 members for product "M", why is the below formula not working, kindly suggest a better approach;

GroupsWithMoreThanFiveMembersAndProduct is M =
CALCULATETABLE(
   SUMMARIZE(
       FILTER(
           'Sales',
           'Sales'[Product] = "M"
       ),
       'Sales'[Group ID],
       "Count", COUNT('Sales'[Customer Number])),
   [Count] > 5
)
  • eliasayyy's avatar
    eliasayyy
    2 years ago

    can you please try 

    GroupsWithMoreThanFiveMembersAndProduct is M =
FILTER(
    CALCULATETABLE(
        SUMMARIZE(
            'Sales',
            'Sales'[Group ID],
            "Count", COUNT('Sales'[Customer Number])
        ),
        'Sales'[Product] = "M"
    ),
    [Count] > 5
)
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    hi hosea_chumba ,

     

    try like:

    GroupsWithMoreThanFiveMembersAndProduct is M =
FILTER(
    ADDCOLUMNS(
       SUMMARIZE(
            'Sales',
            'Sales'[Product], 
            'Sales'[Group ID]
        ),
        "Count", 
        CALCULATE(COUNT('Sales'[Customer Number]))
    ),
    [Count]>5&&'Sales'[Product] = "M"
)

2 Replies

  • eliasayyy's avatar
    eliasayyy
    Memorable Member
    2 years ago

    can you please try 

    GroupsWithMoreThanFiveMembersAndProduct is M =
FILTER(
    CALCULATETABLE(
        SUMMARIZE(
            'Sales',
            'Sales'[Group ID],
            "Count", COUNT('Sales'[Customer Number])
        ),
        'Sales'[Product] = "M"
    ),
    [Count] > 5
)
  • FreemanZ's avatar
    FreemanZ
    Super User
    2 years ago

    hi hosea_chumba ,

     

    try like:

    GroupsWithMoreThanFiveMembersAndProduct is M =
FILTER(
    ADDCOLUMNS(
       SUMMARIZE(
            'Sales',
            'Sales'[Product], 
            'Sales'[Group ID]
        ),
        "Count", 
        CALCULATE(COUNT('Sales'[Customer Number]))
    ),
    [Count]>5&&'Sales'[Product] = "M"
)