Forum Discussion

SAPpowerbi's avatar
SAPpowerbi
Helper II
3 years ago
Solved

duplicate values count based on another column values

Hello Team ,   I want count based on business line  for category 1,Please help me to write a dax column . Please find below tabel for example. I want output based on category 1 and business Line ...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, SAPpowerbi 

     

    You can try the following methods.

    Column =
CALCULATE (
    COUNT ( 'Table'[Category 1] ),
    FILTER ( 'Table', [Category 1] = EARLIER ( 'Table'[Category 1] ) )
)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

johnt75's avatar
johnt75
Super User
3 years ago

Try

Calc column =
CALCULATE (
    COUNTROWS ( 'Table' ),
    ALLEXCEPT ( 'Table', 'Table'[Category 1], 'Table'[Business Line] )
)
  • SAPpowerbi's avatar
    SAPpowerbi
    Helper II
    3 years ago


    Hi thank you for quik responce .

    Need small help on the same dax = like category1  A two times and business line is Export and Import so my expected count is 2 , as per your dax its counting 1 

     

    Your dax output is like below =

     

    Category 1Business Linecalculated column
    AExport 1
    AImport 1

     

    I need below output 

     

    Category 1Category 2Business Linecalculated column
    AABExport 2
    AAcImport2

     

    *** Note - I want Category1 with multiple business line so it will give me count .

    I want output like above kindly help , Thnk you .

    • johnt75's avatar
      johnt75
      Super User
      3 years ago

      Try changing it to

      Calc column =
CALCULATE (
    COUNTROWS ( 'Table' ),
    ALLEXCEPT ( 'Table', 'Table'[Category 1] )
)