How to show only employees with non-zero sales?

[meddojeddo]

I am asuing Azure cube data source and the measures are created on there. I have a sales table which has a relationship with an employee table. The sales table has a sales sum measure. I am creating a report which has a table showing sum of sales for each employee. But the issue I am having is that it shows employees with 0 sales. The sales sum measure doesn't have the option to not show items with no data. How would I go about hiding showing only the employees with non-zero sales? Am I required to only use columns for the sales table?

[v-yiruan-msft · Accepted solution]

Hi @meddojeddo ，

You can select the related visual and navigate to Filters pane to filter out the value which equal to 0, set the filter condition as below screenshot:

In addition, you can update the formula of the sales sum measure with add the following condition. Then the visual will filter out the data with blank values.

sales sum measure =
VAR _ssales =
SUM ( 'sales'[sales] )
RETURN
IF ( var_ssales = 0, BLANK (), var_ssales )

Best Regards

[PaulDBrown]

Assuming you are using the employees table as the filter context in your visual, try

Sales <> 0 = CALCULATE([Sum sales], FILTER(employee table, [Sum sales] > 0))

or

sales <>0 = IF([Sum sales] > 0, [Sum sales])

[meddojeddo]

@PaulDBrownthe sales measure is actually in the sales table and not the employee table. But the employee dimension column is from the employee table. Is there a way to display only the employees with data in the sales table or would I need to use a different custom measure for that?

[PaulDBrown]

if it is a measure it doesn't matter what table it is in