After adding column with totals without a specific filter, blank rows appear. DAX adjustment needed

[Anonymous]

Dear members,

Ok this is my first post, so, I don't know yet how to upload my example file, so I'll try to explain the problem as clearly as possible, but it all comes down to not understanding the way the filtering works and how to get the desired info, easily.

So, I have the following 6 tables:

1. Category table with category number and name;
2. Client table with client number and client name;
3. Date table;
4. Employee table with employee name and number;
5. Table for invoice lines with employee number, client number, category number, date and invoice amount;
6. Table for written time with employee number, client number, category number, date, hours written and the amount that represents the written time (hours x price).

The 6 tables are properly linked together.

In the page in the report section, I enabled a filter for the first employee and a filter for a date range.

So, I made a table in the report section with the following columns:

1. Employee Name from the Employee table;
2. Client name from the client table;
3. Total hours from the written time table;
4. Total amount to be charged from the written time table;
5. Measure that retrieves the invoice amount without the Employee filter, but with a filter for the category embedded in the DAX formula.

The measure mentioned under 5), has the following DAX code 

Invoice amount without Employee filter = CALCULATE( SUM('Invoice lines'[Invoice amount]), ALL(Employees[Employee Name]), Categories[Categorynumber] = "01")

With the current configuration, I retrieve a table in which only the rows for the first employee are visible, but the rows are not limited to the clients that the first employee wrote time for. Instead it returns all clients that have invoice lines with category number "01".

In my example, there is only one client for which the first employee did not write time for, but in my actual model, it returns multiple lines that are not relevant for the employee, because he did not write time on those clients.

So, what I wanted to retrieve, was a table with the clients for employee 1 with the hours written and amount chargeable that is related to employee 1, compared to the total invoiced amount for that specific client of which the invoice lines only refer to category number "01", but instead of only calculating the invoiced amount that relates to employee 1, it should represent the invoiced amount for all employees.

Of course when I export the table to Excel and manually the lines that I don't need, is also an option, but that is not as smooth as I would prefer, because I would prefer a way to compile the complete desired table without the excess rows that are not relevant for employee 1.

So, anyone who has a working solution/method?

[Anonymous]

Sometimes, it can be so easy.... I tried filters on the page, but that did not work. putting the filter on the visual level, did the trick. Thank you!

[johnt75 · Accepted solution]

Put a filter on the visual to only show rows where the total hours is > 0.