New keyword vs Common Keyword

[Arvind_123]

Hi Team,

I have raise this query in power bi community but not got answer.
can you please help me with this?

I have table BT3 Keyword (2) that have column name like Month Id, Management Group, Keyword, Source Name and from this I want to check New keyword count in every current month.. Suppose if Keyword is present in the current month and has no value in any previous month then make it as "New" and If current month keyword present in any of previous month keyword make it as common.

so I have made dax for it .. it is working fine with overall new distictcount when no management group selelcted but when I select any of management group it is giving me lesser value as compare to Excel. Is it beacuse of the keyword is present for other mangement group too ? that is the why my dax not working properly ?

I want every keyword should be count for every management group level.

can you please help me to solve this query?

I have used this dax query to find out the New count in power bi desktop:

step 1: I have created a calculated column as to find New vs Common:

KeywordStatus = IF ( NOT ( COUNTROWS ( FILTER ( ALL('BT3 Keyword (2)'), 'BT3 Keyword (2)'[Keyword] = EARLIER('BT3 Keyword (2)'[Keyword]) && 'BT3 Keyword (2)'[Month Id] < EARLIER('BT3 Keyword (2)'[Month Id]) ) ) ), "New", "Common" ) ​

step 2:
I have created a measure to find out Distictcountof New keyword as:
NewC = CALCULATE( DISTINCTCOUNT('BT3 Keyword (2)'[Keyword]), FILTER( 'BT3 Keyword (2)', 'BT3 Keyword (2)'[KeywordStatus] = "New" ) )

Thanks,

Arvind

[v-cgao-msft]

Hi @Arvind_123 ,

Please consider providing a file without sensitive data and posting the expected output, it would be very helpful, thank you.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Arvind_123]

Hi @v-cgao-msft ,

This is the image view.

Sippose this is the input table that I have:

Input

Output I need of New Keyword Distinct Count of all sourec where project I have select as A for filter.

Output

Note : New keyword means that the keyword is present in current month but not present in any previous month.

Thanks,

Arvind

[Arvind_123]

Hi @v-cgao-msft ,

Thanks for reply!
This is the input table.

Month Id	Year Month	Project	Keyword	Source Name	Revenue
12	202312	A	medical asst columbia ce	Aditya	9
12	202312	A	top ai stocks to invest in	Aayansh	8
12	202312	B	urgent jobs hiring now	Anand	8
12	202312	B	upcoming stock-split stocks to buy	lallan	4
12	202312	C	causes of guillain barré syndrome	Aditya	9
11	202312	C	Big and Tall Mens Clothing Online	Aayansh	7
11	202312	C	medical asst columbia ce	Anand	3
11	202312	D	top ai stocks to invest in	lallan	9
11	202312	D	upcoming stock-split stocks to buy	Aditya	6
11	202312	E	is 5g internet available in my area	Aayansh	1
11	202312	E	top ai stocks to invest in	Anand	4
10	202312	F	jefferson standard life insurance	lallan	8
10	202312	F	medical asst columbia ce	Aditya	4
10	202312	B	who does 40 year mortgages	Aayansh	8
10	202312	B	doctors who treat osteoarthritis	Anand	7
10	202312	B	top ai stocks to invest in	lallan	5
9	202312	A	upcoming stock-split stocks to buy	Aditya	9
9	202312	A	cheapest way to lay artificial grass	Aayansh	6
9	202312	A	income based senior living in city	Anand	10
9	202312	D	upcoming stock-split stocks to buy	lallan	10
9	202312	C	top ai stocks to invest in	Ayansh	7

output I needed is New Keyword distinct count source wise :
Here suppose  I have selected on slicer -- project as A then what is the new keyword count of that particular project A on source level looks like this based on above Input table :

	Project	A
	Month Id
Source	12	11	10	9
Aditya	1	0	0	1
Aayansh	1	0	0	1
Anand	0	0	0	1
lallan	0	0	0	0
Ayansh	0	0	0	0
Total	2	0	0	3

Note : New keyword means that the keyword is present in current month but not present in any previous month.

Please let me know if you need any further details.

Thanks ,
Arvind