topic Re: Applying filters for unique ID's per month, using only the latest instance of an ID in DAX Commands and Tips

Applying filters for unique ID's per month, using only the latest instance of an ID

Anonymous — Thu, 25 Jan 2024 19:14:26 GMT

I have a set of data with serveral unique ID's, that may appear several times in the dataset. We want to see how many unique ID instances exist every month, which is done by this formula

Total Per Month=COUNTROWS(SUMMARIZE('Table1', 'Date Table'[Date Month],'Table1'[ID]))

These ID's have possilbe tags of Red, Green, and Blue. We want to be able to see how many total every month for each respective color tag. The thought process behind this was through this measure

Total Green=CALCULATE([Total Per Month],'Table1'[Color]="Green")

However, within an indiviual month, the color of the ID's can change which will create a duplicate, thus it will miscount the total of that color for months where the color changes. I want to be able to have it only count the color of the most recent date, which is indicated by a column called "Last Updated", which shows the most recent date iteration of the ID and it's respective color

Any help would be greatly appreciated!

Re: Applying filters for unique ID's per month, using only the latest instance of an ID

some_bih — Thu, 25 Jan 2024 20:28:41 GMT

Hi Anonymous it is not totally clear to me what is your request.

Usually, it it good practice to see how some fact is changed using Calendar / Date table.

On link you can see how much different product key (products) with different colors are sold per year month

I hope this help in your situation as idea, otherwise share your model with simple example and expected output

Re: Applying filters for unique ID's per month, using only the latest instance of an ID

Anonymous — Thu, 01 Feb 2024 07:00:53 GMT

Hi, Anonymous

May I ask if you have solved your problem, you can try to use some_bih's approach, if not, you can either share the pbix file without sensitive data or post a full reproduction of the problem including information about the data table, measurements, expected effect and the logic to achieve the effect.

Best Regards,
Yang
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