Forum Discussion
Post PartisanNeed help joining a table to filter values as not active
Hi, I need to show a list of the counties that were not active in a quarter. I have a full county dimension set (as it's not in my CRM), then I have my data table. I already have a measure that gives me the exact number of active local authorities per quarter. I just need to link the dimension set and show the counties NOT active per quarter. Ho can this be done please?
All Counties Table
| Counties_All |
| Bedfordshire |
| Berkshire |
| Bristol |
| Buckinghamshire |
| Cambridgeshire |
| Cheshire |
This is my data set (Bristol is still active as it has 1 property active):
| Counties | Property | Created_date | Decommissioned_Date |
| Bedfordshire | L1 | 01/04/2024 | 05/10/2024 |
| Bedfordshire | L2 | 01/04/2024 | 05/10/2024 |
| Bristol | L3 | 01/04/2024 | |
| Bristol | L4 | 01/04/2024 | 01/05/2024 |
| Buckinghamshire | L5 | 01/04/2024 | 01/09/2024 |
| Buckinghamshire | L6 | 01/04/2024 | 01/09/2024 |
| Buckinghamshire | L7 | 01/04/2024 | 01/09/2024 |
| Cambridgeshire | L8 | 01/04/2024 | 01/08/2024 |
| Cambridgeshire | L9 | 01/04/2024 | 01/08/2024 |
| Cheshire | L10 | 01/04/2024 |
For example, I need the counties not active to show:
| Bedfordshire |
| Berkshire |
| Buckinghamshire |
| Cambridge |
RichOB - Make a relationship between your All counties table, and your data table, then use the measure you have that counts the active counties on a table visual (with the column from the dimension), and set the meausre to "is blank"
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
6 Replies
- mark_endicott
Super User
RichOB - Make a relationship between your All counties table, and your data table, then use the measure you have that counts the active counties on a table visual (with the column from the dimension), and set the meausre to "is blank"
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
Hi RichOB,
Just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If so, it would be really helpful for the community if you could mark the answer that helped you the most. If you're still looking for guidance, feel free to give us an update, we’re here for you.Best Regards,
Hammad.
Hi RichOB,
As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If so, it would be really helpful for the community if you could mark the answer that helped you the most. If you're still looking for guidance, feel free to give us an update, we’re here for you.Best Regards,
Hammad.
Hi RichOB,
I just wanted to follow up on your thread. If the issue is resolved, it would be great if you could mark the solution so other community members facing similar issues can benefit too.
If not, don’t hesitate to reach out, we’re happy to keep working with you on this.Best Regards,
Hammad.
- Ashish_Mathur
Hi,
Assuming there is a relationship (Many to One and Single) from Table2 to Table1, to your Table visual, drag Countries from Table1 and write this measure
Measure = calculate(countrows(Data),Data[Decommissioned_Date]=blank())
Hope this helps.
