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
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:
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:
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):
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?
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?
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
104 | |
102 | |
87 | |
61 |
User | Count |
---|---|
162 | |
132 | |
131 | |
95 | |
86 |