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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors