Lookup targets and sum from disconnected table

[pmcmonag]

I have been using a DAX measure to harvest slicer selections (in example: Sales Area, Financial Quarter Year), create keys on the fly and use these to then filter a disconnected Targets table and sum the relevant values. 

New Clients Target MEASURE =

VAR Teams = VALUES('Client List'[Sales Area])

VAR selectedQuarter = VALUES('Date'[Financial Quarter Year])

VAR temptable = ADDCOLUMNS(CROSSJOIN(Teams,selectedQuarter),"TargetKey", CONCATENATE('Client List'[Sales Area],'Date'[Financial Quarter Year]))

VAR Tkeys = SUMMARIZE(temptable,[TargetKey])

RETURN

CALCULATE(SUM('Targets'[New Clients Target]),FILTER(ALL('Targets'),'Targets'[Key] IN Tkeys))

I had previously hardcoded 'sales area-quarter KEYS' and used relationships between the tables but ran into issues when selecting multiple teams and quarters, so the above approach seemed the most suitable. 

Now the issue I'm having, being in a new financial year there are some Sales Areas who have yet to make a sale, and therefore the data driven measure means their target will not be calculated so returns as a blank. 

So this works fine, but one Sales Area = Belfast, has had no sales (Count of New Clients) , and also the Target measure does not get calculated so it is not displayed. In my actual report, this target is displayed as a Gauge visual. Ideally when Belfast is selected the TArget could still be shown rather than returning blank.

I've tried many options to no avail, so was hoping someone may be able to suggest something or a different approach? Thanks ins advance. 

.pbix example file

[Anonymous]

@pmcmonag -

If you want the measure to be blank in tables yet not blank in cards, you'll need a second measure, like:

New Clients Target MEASURE Non Blank = IF(ISBLANK([New Clients Target MEASURE]),0,[New Clients Target MEASURE])

Cheers!

Nathan

[pmcmonag]

Thanks for the suggestion Nathan, though it's not that I want the measure to return a 0 instead of a blank. Basically regardless of whether there are any data rows I want the Target measure (hence table) to still be able to return it's value.

When I select Belfast, the value for the current selections should return 1. However due to the lack of New client in the sales table, the target measure is returns either a blank, or a zero as per your IF suggestion. 

I want to be able to see a Sales Areas target regardless of whether a sale has occured within the selected time period. 

I'm struggling to articulate this problem, hopefully the screenshot below helps...

[Anonymous]

@pmcmonag  - Why is targets disconnected? You could make a many-to-many relationship to Targets from each of the date and Team tables. Then you don't need to do too much work in DAX.