duplicate values count based on another column values

[SAPpowerbi]

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 column .

Category 1	Category 2	Business Line
A	AB	Export
A	AC	Import
B	AA	Export
C	AM	Export
D	AA	Export
D	BB	Import
D	CC	Export

Please find below expecated output .

Category 1	Ctegory 2	Business Line	Calculated column
A	AB	Export	2
A	AC	Import	2
B	AA	Export	1
C	AM	Export	1
D	AA	Export	3
D	BB	Import	3
D	CC	Export	3

In above output categort 1 - is "A" so A is come 2 times under business line -Import and export so my count is "2".

same for Cetegory 1  - "C" come 1 time under business line is only "Export" so my count is "1" .

Please help me to write a dax calulted column .

Thank you in advance . 

[v-zhangti · Accepted solution]

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.

[philouduv]

Hey there,

Try this : 

new_column = Calculate(COUNTROWS(Table),FILTER(Table,

Table[Category 1] == EARLIER(Table[Category 1])))

In red is what you have to adapt to your data model.

Best regards,

[johnt75]

Try

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

[SAPpowerbi]

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 1	Business Line	calculated column
A	Export	1
A	Import	1

I need below output 

Category 1	Category 2	Business Line	calculated column
A	AB	Export	2
A	Ac	Import	2

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

I want output like above kindly help , Thnk you .

[johnt75]

Try changing it to

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