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
UserPBI
Frequent Visitor

Creating measure to count ids based on a filter previously applied

Hi all!

I have the following scenario:
Table A has 4 Columns
ID, Product ID, Salesnumber, CreatedDateTime

I'll have to filter the report by one specific ID initially, so we will be seeing the visuals for one specific ID always and of course I will have the name of the Product for that ID and the salenumber showing on simple card visuals (as result of the filter applied). Now, based on that filter applied initially, I would like to have:
1- A measure that would show me the count of IDs for past 5 days(for instance) - that are associated to the Product ID that is showing (as we filtered by one specific ID )

Let me know if the above is clear, I would be more than happy to share an example if required. I've been struggling with this and I can't find an answer on how to do it.

Thank you in advance!

3 REPLIES 3
vapid128
Solution Specialist
Solution Specialist

CALCULATE(

    DISTINCTCOUNT([Product ID],

    Filter(ALL('Table A'),

        'Table A'[CreatedDateTime]>MAX('Table A'[CreatedDateTime]) -5

    )

)

vapid128
Solution Specialist
Solution Specialist

count of IDs means DISTINCTCOUNT of IDs

past 5 days means the Latest of CreatedDateTime

 

 IDs for past 5 days = 

CALCULATE(

    DISTINCTCOUNT([ID],

    Filter(ALL('Table A'),

        'Table A'[CreatedDateTime]>MAX('Table A'[CreatedDateTime]) -5

    )

)

This doesn't cover the part that this distinct count would be for the specific product ID that was filtered in the beginning.

Example:

UserPBI_0-1658870437546.png

 
ID 8 gets selected -> Product ID is 101 -> what is required is the count distinct of Ids for that specific Product ID in last 5 days.
Is it possible?

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.

Top Solution Authors