Forum Discussion
Helper VApply filter but not exclude all
For the data below , the expectation is to display all the members and the number of contacts they had. But I have to use the contact date as a slicer.
| PersonID | Name | ContactDate |
| A123 | Jim | 1/1/2023 |
| A234 | Steve | 1/2/2023 |
| A345 | Mike | 2/5/2023 |
| A346 | Jenna | 4/5/2023 |
| A347 | Mary | 6/4/2023 |
| A348 | Phil | 1/6/2023 |
| A349 | Mark | |
| A350 | Bono | |
| A351 | Bill | 4/1/2023 |
| A352 | Scott | |
| A353 | Dave |
So when i display the date , i get only the persons with the contact date. I tried using all except but the display of slicer removed the 4 other individuals without contact.
The expectation is
| PersonID | Name | CountofContact |
| A123 | Jim | 1 |
| A234 | Steve | 1 |
| A345 | Mike | 1 |
| A346 | Jenna | 1 |
| A347 | Mary | 1 |
| A348 | Phil | 1 |
| A349 | Mark | 0 |
| A350 | Bono | 0 |
| A351 | Bill | 1 |
| A352 | Scott | 0 |
| A353 | Dave | 0 |
Any solution please.
Hi PBI5851 ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create measures.
CountofContact = IF(MAX('Table'[ContactDate]) <> BLANK(),1,0)Measure = IF(COUNTROWS(ALL('Table'[PersonID]))=COUNTROWS(VALUES('Table'[PersonID])),COUNTROWS(VALUES('Table'[PersonID])),SUMX(ALLSELECTED('Table'),[CountofContact]))(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
I assume you are using Table?
PersonID | Name | ContactDate
with count on the contactDate?
If so, you can just right click on Name or PersonID (either should work) and select "Display items with no data".
PBI5851vhsn
Will try that, but the other issue which i forgot to mention is i have a card that is supposed to read 11 members (as in total count of members) and then change when slicer of date is moved. So opening the report, and date slicer not being "touched" (indicating full range), the card should be 11 and then 4 if date slicer range is selected from 1/1/23 - 3/1/23
Is that even possible ?
Hi PBI5851 ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create measures.
CountofContact = IF(MAX('Table'[ContactDate]) <> BLANK(),1,0)Measure = IF(COUNTROWS(ALL('Table'[PersonID]))=COUNTROWS(VALUES('Table'[PersonID])),COUNTROWS(VALUES('Table'[PersonID])),SUMX(ALLSELECTED('Table'),[CountofContact]))(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
