Forum Discussion

nick9one1's avatar
nick9one1
Helper III
1 year ago
Solved

slicing a table between two dates

I have a table of assets. Each asset has a start and end date (when it was new and when it broke or stopped working). My report has a date dimewnsion table (dim_date), that I use in the slicer to se...
Need Help
  • V-yubandi-msft's avatar
    V-yubandi-msft
    1 year ago

    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'.

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.

 

nick9one1's avatar
nick9one1
Helper III
1 year ago

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