FIltering a Shape Map with a Measure that returns a string

[sirgseymour]

Hello,

I have a data source that has a number of tables in it. What I am trying to acheive is to select a particular date which will then filter a grid and shape map whose values lie within a range around the selected date. The rows in the table are a series of contracts that have a Contract Effective Date and a Contract Expiration date. If a particular date is selected, then the measure will determine which contracts to include in the report. The rule is as follows:

• Contract Effective Date <= Selected Date < Contract Expiration Date

Basically if the Contract Effective Date is Less than the Selected Date AND the Contract Expiration Date is larger than than the Selected Date, then the contract information will be displayed in teh Grid and in teh Shape Map. The issue that I am having is that the filtering works for the Grid but not the Shape Map and I am not sure why. The table structure is as follows:

• Date Table created using the following: Date_Table = ADDCOLUMNS(calendar(date(YEAR(TODAY())-1,1,1),date(2031,1,1)),"DateAsInteger",FORMAT([Date],"yyyymmdd"),"Month",format(DATE(year([Date]) ,Month([Date]),1),"MMMM"),"Year",YEAR([Date]),"Month Number",Month([Date]))
• Contracts: This contains the Contract data

In the report I created a slicer with the setting style as After. The data source for the slicer is the date table. This is where the user will select the date that they want to return the contracts for.

A sample of the Contracts table is below: 

EffectiveDate	ExpirationDate	ContractId	State	Value
01-01-23	05-01-23	1	TX	100,000
02-01-23	06-01-23	2	TX	125,000
03-01-23	07-01-23	3	TX	150,000
04-01-23	08-01-23	4	TX	175,000
05-01-23	09-01-23	5	TX	200,000
06-01-23	10-01-23	6	TX	225,000
07-01-23	11-01-23	7	TX	250,000
08-01-23	12-01-23	8	TX	275,000
09-01-23	13-01-23	9	TX	300,000
10-01-23	14-01-23	10	TX	325,000
11-01-23	15-01-23	11	TX	350,000
12-01-23	16-01-23	12	TX	375,000
13-01-23	17-01-23	13	TX	400,000
14-01-23	18-01-23	14	TX	425,000
15-01-23	19-01-23	15	TX	450,000
16-01-23	20-01-23	16	TX	475,000
17-01-23	21-01-23	17	TX	500,000
18-01-23	22-01-23	18	TX	525,000
19-01-23	23-01-23	19	TX	550,000
20-01-23	24-01-23	20	TX	575,000
21-01-23	25-01-23	21	TX	600,000
22-01-23	26-01-23	22	TX	625,000
23-01-23	27-01-23	23	TX	650,000

I created a measure in the Contract Table to define on the fly whether a particular contract was Inforce (satisfied Contract Effective Date <= Selected Date < Contract Expiration Date) or Not Inforce ( did not satisfy Contract Effective Date <= Selected Date < Contract Expiration Date):

IncludedContracts =

var filterSelectedDate = DATE(Year(FIRSTDATE(Date_Table[Date])),MONTH(FIRSTDATE(Date_Table[Date])),DAY(FIRSTDATE(Date_Table[Date])))

var effectiveDateFromRow = SELECTEDVALUE(Contracts[EffectiveDate])

var effectiveDate = DATE(Year(effectiveDateFromRow),MONTH(effectiveDateFromRow),DAY(effectiveDateFromRow))

return  if(effectiveDate <= filterSelectedDate && filterSelectedDate < SELECTEDVALUE(Contracts[ExpirationDate]), "Inforce", "Not Inforce")

I set up a grid with the Effective Date, Expiration Date and the Contract ID and the added the Included Contracts measure into the Filters for that visual - the correct Contracts were filtered out.

I added a Shape Map to the report and added State as the location, Value as the Colour saturation and the added the Included Contracts measure into the Filters for that visual but the correct Contracts were not filtered. 

Any ideas?

[sirgseymour]

It was the one suggested to me. Do you think there is a better solution?

[lbendlin]

if all you have is "TX" then put your data in a table visual, or a column chart with play axis etc.  What is the business question that you are trying to answer?

[lbendlin]

Why use a shape map if you only have one geo field?

[sirgseymour]

So in my full data set there are values fro all 50 states at a state level. The idea is to to have a numerical grid and a Shape Map. The shape map has a drill through to a second page that has a numerical grid and a shape map at US County level. The shape maps allow the user to hover over each state or county and then drill through to the next level of map. All other widgets (grids, etc...) filter properly when the Inforce selection is made but the shape map does not filter by the values Measure for Inforce.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.