Need help joining a table to filter values as not active

[RichOB]

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

[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.

[v-mdharahman]

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.

[v-mdharahman]

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.

[v-mdharahman]

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.

[v-mdharahman]

Hi @RichOB,
We noticed there hasn’t been any recent activity on this thread. If your issue is resolved, marking the correct reply as a solution would be a big help to other community members. If you still need support, just reply here and we’ll pick it up from where we left off.

Best Regards,

Hammad.

[mark_endicott · Accepted solution]

@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!