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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors