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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Nisha357
New Member

Restrict output only for dates in dataset

User Status Measure =
SWITCH(
    TRUE(),
    -- Mark as "Active" if conditions for activity are met
    MAX(T[Date]) > CALCULATE(MIN(T[First Occurrence Date]), ALLEXCEPT(T, T[FN]))
    && MAX(T[Date]) >= DATE(2024, 10, 01),
    "Active",
   
    -- Mark as "Inactive" only for rows between 2024-10-01 and today
    MAX(T[Date]) >= DATE(2024, 10, 01) &&
    MAX(T[Date]) <= TODAY(),
    "Inactive",
   
    -- Exclude all rows outside the defined conditions
   BLANK()
)
I have this measure for Inactive users it shows past and future dates i want only dates which are in dataset for both active and inactive users i tried putting filters in visual but it does not work. Please help here.
1 ACCEPTED SOLUTION
DAXian
Frequent Visitor

You need to clarify your date conditions. 
 
MAX(T[Date]) > CALCULATE(MIN(T[First Occurrence Date]), ALLEXCEPT(T, T[FN]))
    && MAX(T[Date]) >= DATE(20241001),

This filter will consider any record for as long as CALCULATE(MIN(T[First Occurrence Date]), ALLEXCEPT(T, T[FN])) is below or equal to MAX(T[Date]).
foE example :
MAX(T[Date])CALCULATE(MIN(T[First Occurrence Date]), ALLEXCEPT(T, T[FN]))DATE(20241001)Satisfy first condition?Satisfy Second Condition?Is Active?
April 1, 2025February 2, 2025October 1, 2024 YYY
April 1, 2025April 12, 2025October 1, 2024 NYN
April 1, 2025August 1, 2024October 1, 2024YYY
April 1, 2025December 23, 1950October 1, 2024YYY

 

If the above is correct, then we can focus on Inactive.

MAX(T[Date]) >= DATE(20241001) &&
    MAX(T[Date]) <= TODAY(),
So as long as it is between Today and October 10, 2024 inclusive, it is inactive?
This sets a confusion. Other records that should be inactive can be included in the active. Kindly provide more details for Inactive records.



View solution in original post

5 REPLIES 5
v-pnaroju-msft
Community Support
Community Support

Hi Nisha357,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.


Thank you.

DAXian
Frequent Visitor

You need to clarify your date conditions. 
 
MAX(T[Date]) > CALCULATE(MIN(T[First Occurrence Date]), ALLEXCEPT(T, T[FN]))
    && MAX(T[Date]) >= DATE(20241001),

This filter will consider any record for as long as CALCULATE(MIN(T[First Occurrence Date]), ALLEXCEPT(T, T[FN])) is below or equal to MAX(T[Date]).
foE example :
MAX(T[Date])CALCULATE(MIN(T[First Occurrence Date]), ALLEXCEPT(T, T[FN]))DATE(20241001)Satisfy first condition?Satisfy Second Condition?Is Active?
April 1, 2025February 2, 2025October 1, 2024 YYY
April 1, 2025April 12, 2025October 1, 2024 NYN
April 1, 2025August 1, 2024October 1, 2024YYY
April 1, 2025December 23, 1950October 1, 2024YYY

 

If the above is correct, then we can focus on Inactive.

MAX(T[Date]) >= DATE(20241001) &&
    MAX(T[Date]) <= TODAY(),
So as long as it is between Today and October 10, 2024 inclusive, it is inactive?
This sets a confusion. Other records that should be inactive can be included in the active. Kindly provide more details for Inactive records.



v-pnaroju-msft
Community Support
Community Support

Hi Nisha357,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

v-pnaroju-msft
Community Support
Community Support

Thank you, @bhanu_gautamfor your response.

 

Hi Nisha357,

 

We would like to check if the solution provided by @bhanu_gautam has resolved your issue. If you have found an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.

If you found the response helpful, please mark it as the accepted solution and add kudos. This recognition benefits other members seeking solutions to similar queries.

 

Thank you.

bhanu_gautam
Super User
Super User

@Nisha357 , Try using

DAX
User Status Measure =
SWITCH(
TRUE(),
-- Mark as "Active" if conditions for activity are met
MAX(T[Date]) > CALCULATE(MIN(T[First Occurrence Date]), ALLEXCEPT(T, T[FN]))
&& MAX(T[Date]) >= DATE(2024, 10, 01)
&& CONTAINS(T, T[Date], MAX(T[Date])),
"Active",

-- Mark as "Inactive" only for rows between 2024-10-01 and today
MAX(T[Date]) >= DATE(2024, 10, 01)
&& MAX(T[Date]) <= TODAY()
&& CONTAINS(T, T[Date], MAX(T[Date])),
"Inactive",

-- Exclude all rows outside the defined conditions
BLANK()
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.