Forum Discussion
Helper IVFilter on Count is not one
I have two tables in my dataset that are linked via the 'element' column. Next I created the below measure that will show a '1' if there if there is a link, and otherwise the measure remains empty. When I add this measure in my report to a table it works perfectly. I show the control name, and then it shows a '1' if there is a link, and remains empty if there is no link. But now I try to filter the table so that it only shows control names when there is no link. I tried all options like 'Is empty' 'Is less then 0' etc. But what ever option I try the table remains empty. What do I need to do to make this work?
I also tried the other way around, so the same field, and then filter on 'Is' '1'. and then it indeed shows the control names were the count is 1.
Controls
| Name | Element |
| control1 | Element2 |
| control2 |
Issues
| Name | Element |
| Issue1 | Element2 |
| Issue2 | Element2 |
Hi Roym ,
Please try to add "+0" with your measure like so:
Count = CALCULATE ( DISTINCTCOUNT(Table_Issues[Name]),KEEPFILTERS (Table_Controls[Name] <> "0")) + 0Then filter with "is 0" and you will get what you want.Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- Icey
Community Support
Hi Roym ,
Please try to add "+0" with your measure like so:
Count = CALCULATE ( DISTINCTCOUNT(Table_Issues[Name]),KEEPFILTERS (Table_Controls[Name] <> "0")) + 0Then filter with "is 0" and you will get what you want.Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
RoymThis works perfectly. thanks!!
- amitchandak
Super User
Roym , Not very clear on expected output, check if this measure can help
Count = CALCULATE ( DISTINCTCOUNT(Table_Issues[Name]),
filter (Table_Controls , not(isblank(Table_Controls[Element] )) ) )- Roym
amitchandak Unfortunatly same result. So the table in my report is:
Control name Count Control1 1 Control2 So it shows a '1' if there is a link. That works perfectly, but when I try to filter the results (not equal to 1, is blank etc.) if shows no results at all 😞
- amitchandak
Roym , Try this, Or share the expected output
Count =
var _1 = CALCULATE ( DISTINCTCOUNT(Table_Issues[Name]),
filter (Table_Controls , not(isblank(Table_Controls[Element] )) ) )return
if(Isblank(_1), 1, blank())
