Forum Discussion
Use filter to exclude
Anonymous You need to add to the table an index row. If you don't have, create an index in query mode when you load the table.
On the visual table just add the index column, make sure the defaut calculation for index is 'Do not summarise' on the values as usually will show sum
Hi Anonymous,
In addition, with the help of the Query Parameter, you can also achieve your requirement. When you run the report, you can change prefer parameter value to filter corresponding data rows, instead of setting any report filter or slicer.
In your scenario, you can create a query parameter and list all products. Then set the filter with "does not equal" in Query Editor
like below:
For more information, you can take a look at this article: Deep Dive into Query Parameters and Power BI Templates.
If you have any question, please feel free to ask.
Best Regards,
Qiuyun Yu
jahida Anonymous Really interesting..I was trying to test EXCEPT() for a long time & now is the time..
I create a table 'Sales'
ProductAmount
| A | 5 |
| A | 5 |
| A | 5 |
| B | 5 |
| B | 5 |
| B | 5 |
| C | 5 |
| C | 5 |
a table 'Products'
Product
| A |
| B |
| C |
Then create the relantionship ( one way ) and the formula
Sales = CALCULATE(SUM(Sales[Amount]);EXCEPT(ALL(Products);Products))
Result :
* You need to use the column from sales table, else if use the column from Product it gives you the correct sum but shows the selected product.
Much better than my solution, well done.
jahida Yours also works with previous DAX versions, mine not :)
Thats sweet and simple! But how do i return a table instaed of a calculation
Is there a way to return a subset of a table but using a filter to exclude rows.
example if i have table 1
A 20 B 20 A 30 C 10 B 40 C 10 On using a slicer- when i select "A"- it should return a table
B 20 C 10 B 40 C 10 Anonymous You need to add to the table an index row. If you don't have, create an index in query mode when you load the table.
On the visual table just add the index column, make sure the defaut calculation for index is 'Do not summarise' on the values as usually will show sum
Hi Anonymous,
In addition, with the help of the Query Parameter, you can also achieve your requirement. When you run the report, you can change prefer parameter value to filter corresponding data rows, instead of setting any report filter or slicer.
In your scenario, you can create a query parameter and list all products. Then set the filter with "does not equal" in Query Editor
like below:
For more information, you can take a look at this article: Deep Dive into Query Parameters and Power BI Templates.
If you have any question, please feel free to ask.
Best Regards,
Qiuyun YuIs there a way I can do this in DIrect Query mode, this method can only be done in import mode.
I tried to get to this step but I coouldn't get to the filter pop up window. Could you explain how you got here?