How to filter sum function within Tablix using value within row in Power BI Report Builder

Question

So I already tried asking this on the Microsoft forums but no one responded so ill post it here instead. I'm having trouble with power BI Report Builder for something that feels like it could be pretty simple but isn't and it may be because I don't understand how to reference values that are in different scopes. Basically I have 2 Datasets, one is a dataset that just has jobs listed on it(So like Project Manager, Analyst, that kind of thing) and another that lists hours worked each week by different people, and it includes a column that references what position they are, so it would be a one to many relationship between Job Dataset and Worked Dataset on their column. I have a tablix that is using the Jobs DataSet and what I want to do is have a column that sums up all of the hours worked, but filter it so that it only sums up the ones who their Job column is equal to the Job value of the row on the table, but I just cant get it to work. I can get it to only sum up ones whos Job column matches a static string, and a parameter, but I just cant for the life of me get it to use values within the row itself.

I've tried directly just clicking and adding the fields from the dataset in the expression window, that gives me an error because I cant use "First" in the aggregation and that's what gets put there by default. I also tried manually messing around with it and changing the function and how its trying to reference the different columns to no luck. The best I managed is to get it to check against a static string or a parameter, but this causes the value to be the same for all rows where I want it to be a different value based on what row it is on.

Anyone have any help? Thanks!

Answer 1

I figured this out. Essentially you must do it in DAX by adding a column and setting that column to "CALCULATE(SUM())" and then typical dax filters if you want to filter further. Easy way to build this with ought having to write basically any dax is to go into the online version of Report Builder, Make the table there like you would in power BI, save it, go into the view mode, go to file, click "Edit in report builder" and then in the new document you can look at the dataset's query and it will be basically made for you, or you can use power BI itself go to "Optimize" tab on the ribbon, selected "Performance Analyzer", In the new side bar click start recording, then click refresh visuals, and then you can see the dax query that the power BI report was doing and edit it a little bit to work with your data and any params. Good luck!