Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TG_12
Frequent Visitor

Count distinct subject by month it first appeared

I have a table similiar to the below:

 

DateTimeRecievedSubjectFolder
01/01/2022RedSquare
23/01/2022BlueCircle
28/01/2022RedSquare
03/02/2022RedSquare
15/02/2022GreenTriangle

 

I want to count the distinct subjects per month but only count the first occurence of the subject with the month it was recieved. I.e. Count 'Red' as 1 and only show it when I filter the month to January (If the month was filtered to Feb do not include red in the coutn of subject)

 

This data will be represented in a bar graph showing 'Number of subjects' by 'Folder' This graoh can then be filtered by month of DateTimeReceived

 

Thanks  

 

5 REPLIES 5
TG_12
Frequent Visitor

@amitchandak  - Thanks for the response.

 

Is the 'Month Year sort' column to be a seperate column from 'Month Year'? 

 

When I try to write both these DAX in the same column I receive a syntax error. 

 

Thanks

@TG_12 , 2 separate columns

 

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c

@amitchandak - Appreciate your assistance here and thank you for sharing that vid on sort columns - very informative! 

 

Unfortunately when using this solution the measure will still count the unique subject as '2' as it appears in 2 months.

 

E.g. using the following example the measure will count 'Red' in both Jan and Feb when I would only like to count 'Red' in Jan as that is when it first appeared

 

Month YearSubject
Jan-2021Red
Jan-2021Red
Feb-2021Red

 

The ideal scneario from this data example would be a count that shows the total unique subjects being = 1 which can then be filtered by month to show Jan-2021 = 1 & Feb-2021 = 0  

Anonymous
Not applicable

fJust to follow the subject

amitchandak
Super User
Super User

@TG_12 , create a month year column and then create meausre

 

column

Month Year = FORMAT([Date],"mmm-yyyy")
Month Year sort = FORMAT([Date],"yyyymm")

 

measure  =

countrows(Summarize(Table, Table[month], Table[Subject]))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.