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'.
Hello nick9one1 ,
You can create a measure which should be similar like this:
Counting = calculate(count(assets), Allselected(academic quarter), 'Assets'[live_status]="alive")
please change column names and other references as per your data tables and columns.
If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!
thanks. Maybe I didnt explain that correctly.
By 'alive' I mean the asset start date is <= the current slicer and asset end date is >= the current slicer. there is no column called alive.
e.g a mirror was installed 01/01/2024 and was broken on the 10/10/2024.
If I select a slicer quarter between those date ranges the mirror should appear in the visual. If I select a quarter after it was broken is should not appear.
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.