Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
sirgseymour
Helper I
Helper I

FIltering a Shape Map with a Measure that returns a string

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: 

 

 

 

EffectiveDateExpirationDateContractIdStateValue    
01-01-2305-01-231TX     100,000    
02-01-2306-01-232TX     125,000    
03-01-2307-01-233TX     150,000    
04-01-2308-01-234TX     175,000    
05-01-2309-01-235TX     200,000    
06-01-2310-01-236TX     225,000    
07-01-2311-01-237TX     250,000    
08-01-2312-01-238TX     275,000    
09-01-2313-01-239TX     300,000    
10-01-2314-01-2310TX     325,000    
11-01-2315-01-2311TX     350,000    
12-01-2316-01-2312TX     375,000    
13-01-2317-01-2313TX     400,000    
14-01-2318-01-2314TX     425,000    
15-01-2319-01-2315TX     450,000    
16-01-2320-01-2316TX     475,000    
17-01-2321-01-2317TX     500,000    
18-01-2322-01-2318TX     525,000    
19-01-2323-01-2319TX     550,000    
20-01-2324-01-2320TX     575,000    
21-01-2325-01-2321TX     600,000    
22-01-2326-01-2322TX     625,000    
23-01-2327-01-2323TX     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?

5 REPLIES 5
sirgseymour
Helper I
Helper I

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

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
Super User
Super User

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

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.

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.