March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi everyone,
I've been trying to create a filter, by creating a new column, to remove rows that have zeroes in all the columns in either a table format or a matrix table format. An example of data is below
Table
Product Sales Costs
A 0 0
B 20 10
C 0 0
D 15 5
Matrix Table
Description Consumer Commercial
Product Sales Costs Sales Costs
A 0 0 15 5
B 20 10 10 3
C 0 0 20 10
D 15 5 10 2
I wrote this formula for a new column:
Please help. Thanks.
Solved! Go to Solution.
You would need to create a calculated measure and use that in the visual filter to get the desired result.
Here's how the calculated measure would look like:
Hope this helps.
Regards,
Tarun
Did I answer your question? Mark my post as a solution!
Hi @Anonymous ,
One sample for your reference.If it doesn't meet your requirement, kindly share your sample data and excepted result to me. Please upload your files to One Drive and share the link here.
I created two calculated column in two tables as below.
Zero = IF(Table1[SALES]=0 && Table1[Costs]= 0,"YES","NO")
Zero1 = VAR SUM1 = CALCULATE(SUM('Table1 (2)'[Costs]),ALLEXCEPT('Table1 (2)','Table1 (2)'[Produce])) VAR SUM2 = CALCULATE(SUM('Table1 (2)'[SALES]),ALLEXCEPT('Table1 (2)','Table1 (2)'[Produce])) RETURN IF(SUM1=0 && SUM2=0,"YES","NO")
Reagrds,
Frank
Thank you both for the solutions. Logic for formulas in DAX is really different; am still trying to get a handle on this. Thank you again.
What is the data field in the Values section of your table/matrix ? Is it an aggregate ? If so, you would need to use a calculated measure instead of calculated column.
Please share the screenshots or the pbix for more inputs.
Thank you for the response. Data field is sales and costs and I can't post a screenshot due to client name in the visualization
You would need to create a calculated measure and use that in the visual filter to get the desired result.
Here's how the calculated measure would look like:
Hope this helps.
Regards,
Tarun
Did I answer your question? Mark my post as a solution!
Thank you for the suggested solution. It didn't work as a measure; to clarify, the formula works but cannot use as a slicer because the inability to put the measure as a slicer but it works as a column; it seems that my formula logic was way off and your suggested solution works great. I just need to wrap my head around how to write proper logic in DAX, it is quite different from what I'm used to in Excel and other ETL and visualization tools.
Before I mark your answer as solution, how would this be accommodate for the matrix array? Let's says Sales and Costs are already rows in the original data but is then pivoted in a matrix table as columns. So in my second matrix tables, those sales and costs are rows and data fields are Consumer and Commercial.
Hi @Anonymous ,
One sample for your reference.If it doesn't meet your requirement, kindly share your sample data and excepted result to me. Please upload your files to One Drive and share the link here.
I created two calculated column in two tables as below.
Zero = IF(Table1[SALES]=0 && Table1[Costs]= 0,"YES","NO")
Zero1 = VAR SUM1 = CALCULATE(SUM('Table1 (2)'[Costs]),ALLEXCEPT('Table1 (2)','Table1 (2)'[Produce])) VAR SUM2 = CALCULATE(SUM('Table1 (2)'[SALES]),ALLEXCEPT('Table1 (2)','Table1 (2)'[Produce])) RETURN IF(SUM1=0 && SUM2=0,"YES","NO")
Reagrds,
Frank
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
147 | |
92 | |
70 | |
58 |