Dax for matrix

[zenisekd]

Hi, 
Could anybody help me out with a following scenario to create an appropriate measure?

I want to put together a matrix, where:
Row = User
Colum = Date
Value = Forecast Measure

I need to calculate the Forecast Measure, so that If I select a "Team" in slicer, it will display only forecasts of users, who do have timesheet in sliced date period (ignoring single Dates, but respecting selected period). 

So if a user has team "A" and forecasts for 1.1. and 1.2. , and Timesheet values only for 1.1. and I select in a slicer team "A" and time period 1.1.-1.2., it will show forecasts for this user for both dates in the matrix for team A... 

In other words, I need the measure to use the timesheet Team slicer in the context, but ignore the timesheet date context .... 

[V-yubandi-msft]

Just checking, @zenisekd , did you get a chance to review my response.  If it meets your requirements and expectations, kindly mark it as the accepted solution so it can assist other community members facing similar issues. Your feedback ensures valuable insights are recognized and shared effectively.

Thank You.

[V-yubandi-msft]

Hi @zenisekd ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[V-yubandi-msft]

Hi @zenisekd ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Ashish_Excel]

Hi,

Share the download link of the PBI file.  Show the expected result there clearly.

[V-yubandi-msft]

Hi @Ashish_Excel ,

The PBIX file containing sample data and the expected behavior is already attached to the original post. I've attached it here again for your convenience. Please feel free to download and review it.

The file demonstrates how the measure filters users based on their timesheet activity within the selected date range, while still displaying forecast values for all relevant dates.

Thank You.

[Ashish_Excel]

Hi,

I cannot understand the expected result at all.

[V-yubandi-msft]

Hi @Ashish_Excel ,

I hope this information was helpful. If you have any further questions or would like to discuss this in more detail, feel free to ask.
For continued discussions and support, please stay connected with the community.

Thank You.

[V-yubandi-msft · Accepted solution]

Hi @Ashish_Excel ,

The Problem We have

1. A Team slicer (Team A, B, etc.)
2. A Date slicer (e.g., 1.1.–1.2.2025)
3. A Matrix visual showing: Rows = Users, Columns=Dates, Values= Forecast

The Challenge We only want to show forecast values for users who

1. Belong to the selected team (from the slicer),
2. Have any timesheet activity within the selected date range.

But here's the twist.

We want to show their full forecast (across all dates), not just on the dates they have timesheets.

If a user selects Team A and sets the date range from January 1 to February 1, the matrix should display:

1. Alice → Forecast for both January 1 & 2
2. Bob → Forecast for both January 1 & 2

Even if Alice only recorded timesheets on January 1, her full forecast for both dates should still be shown. The key point here is that we are not filtering forecasts based on specific timesheet dates instead, the date filter is only used to determine which users were active during the selected period. Once a user has any timesheet activity within the range, we include their entire forecast across all dates.

Note: Please establish a relationship between the Time Sheet table and the Dim User table. Once the relationship is created, you can retrieve the required user details directly through the Time Sheet data.

FYI:

Thanks.

[V-yubandi-msft]

Hi @zenisekd ,

Thank you for engaging with the Microsoft Fabric Community. I tested your requirement with some dummy data and successfully replicated the logic using a measure that filters users based on their timesheet activity within the selected date range, while still displaying forecast values for all relevant dates.

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

[johnt75]

Try

Forecast for users with timesheets =
VAR UsersWithTimesheets =
    CALCULATETABLE ( VALUES ( 'Dim_User'[User ID] ), Timesheets )
VAR Result =
    CALCULATE (
        SUM ( Forecast[Forecast Value] ),
        KEEPFILTERS ( UsersWithTimesheets )
    )
RETURN
    Result

[zenisekd]

Doesnt work, I keep missing value returned by the measure for 2025-05-17, which has no timesheet value,  but has timesheet values in other dates selected in the slicer. Thus should return a forecast value, which the user for given date has.