Forum Discussion

hosea_chumba's avatar
hosea_chumba
Helper I
2 years ago

DAX issue

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
)

1 Reply

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    Hi, hosea_chumba

    hosea_chumba  

    if you want to make new table with group id which has more then 5 customer with product "M"
    try below 
    just adjust your table and column name

     

     

     

    result=
ADDCOLUMNS(
    VALUES(customer[gropuid]),
    "co",
    CALCULATE(
       COUNT(customer[customer]),
       customer[product]="m"
     )
 )

or 
result= 
SUMMARIZE(
     customer,customer[gropuid],
     "cou",
     COUNTX(
          FILTER(
           customer,
           customer[product]="m"
          ),
        customer[customer]
  )
)

     

     

     

    you can use below measure which give you true, false 

     

    Measure 3 = 
 COUNTX(
    FILTER(
       customer,
       customer[product]="m"
    ),
    customer[gropuid]
  )>4

     

     

    out put like