Forum Discussion
slicing a table between two dates
Hi nick9one1 ,
Thank you for engaging with the Microsoft Fabric Community. I tested your requirement with some dummy data and successfully replicated a solution where the bar chart displays all assets that were active during the date range selected via the slicer from the dim_date table.
1. The visual uses this measure as a filter (IsAssetAlive=1) to only include those assets.
2. The bar chart then shows the count of AssetID by AssetName for only the alive assets in the selected time range.
FYI:I’ve attached a sample .pbix file that demonstrates this behavior. Could you please review and confirm if this aligns with your expected outcome?
If my response solved your query, please mark it as the Accepted solution to help others find it easily.
And if my answer was helpful, I'd really appreciate a 'Kudos'.
Hi nick9one1,
Thank you for reaching out in Microsoft Community Forum.
Thank you Kishore_KVN for the helpful response.
Please follow below steps to resolve the issue;
1.Create the measure with the below DAX;
Is Asset Active =
VAR MinSelectedDate = MIN('dim_date'[Date])
VAR MaxSelectedDate = MAX('dim_date'[Date])
RETURN
IF (
MAX('Assets'[StartDate]) <= MaxSelectedDate &&
(ISBLANK(MAX('Assets'[EndDate]) || MAX('Assets'[EndDate]) >= MinSelectedDate)),
1,
0
)
2.Add this measure to the visual filter pane:
--> Set it to show only when Is Asset Active = 1.
This will display assets whose active periods overlap with the selected quarter — for example, a mirror installed in Jan 2024 and broken in Oct 2024 will only appear in visuals when you select quarters that fall within that time range.
Please continue using Microsoft Community Forum.
If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.
Regards,
Pavan.
thanks. I have tried but it's not quite working. 'is asset active' always returns 0
Here you can see I have included the 'is asset active' measure in a table. The min and max dates from the date table asre also shown in cards.
the first row has an asset that should show as '1'
Hi nick9one1,
Thank you for reaching out in Microsoft Community Forum.
Please follow below steps to fix the issue;
1.Create a calculated column (not a measure) in your Assets table with this DAX:
Is Asset Active =
VAR MinSelectedDate = MIN('dim_date'[Date])
VAR MaxSelectedDate = MAX('dim_date'[Date])
RETURN
IF (
'Assets'[StartDate] <= MaxSelectedDate &&
(
ISBLANK('Assets'[EndDate]) || 'Assets'[EndDate] >= MinSelectedDate
),
1,
0
)2.Try this measure instead, which respects the current slicer context and evaluates assets dynamically:
Is Asset Active =
VAR MinSelectedDate = MIN('dim_date'[Date])
VAR MaxSelectedDate = MAX('dim_date'[Date])
RETURN
IF (
'Assets'[StartDate] <= MaxSelectedDate &&
(
ISBLANK('Assets'[EndDate]) || 'Assets'[EndDate] >= MinSelectedDate
),
1,
0
)Please continue using Microsoft Community Forum.
If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.
Regards,
Pavan.These answers seem to be made with a fudemental misunderstanding of powerBi, I wouldnt be surprised if they are Ai coplot generated.
Your first suggestion of a calculated column doesnt work because it cannot refernece the sliced table. It simply looks at the min and max dates in the whole date table.
The second suggestion does not work because you cannot refernce the column 'Assets'[EndDate]) inside RETURN