Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
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])
Proud to be a Super User!
Paul on Linkedin.
@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?
if it is a measure it doesn't matter what table it is in
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |