Uncategorized

terrymriley61

Using Power BI Slicer with a Long List

Power BI Slicer is a great tool to customize reports, but it can be difficult to use with long lists.  One way to fix this is to use two slicers as a hierarchy to speed selection.

Here is an example.  The product list in AdventureWorks is long.  Here is a report that show the Inventory and the pricing for a product selected from the slicer.

singleslicer

The lit is long and its difficult to scroll up and down to find the product one is looking for.  One way to speed the search is to add a hierarchy.  I chose breaking things up by the first letter in the product name.

To do this, I edited the product table by copying the Name table.  Then i split the copy of Product Name into to two columns, with the first letter as the first column.  Then I renamed the column Name A-Z.

Name az

Now add a second slicer using the Name A-Z.  Now you can select the first letter of the product you want, then select the product from the filter product list!  Much easier than scrolling away!

cascading slicer

 

 

Advertisement
terrymriley61

How to return a Zero instead of no Results in SQL Query

Sometimes you want to query to see if an item exists but if it doesn’t you usually get an empty result in SQL.  It would be more useful if it would return a 0.

I often want to ask the question: Does x exist? and get back a yes or no.  Yes or No often translates to boolean as a 1 or 0.  Let say I query to see if a Date is a Holiday.  I have a table of Holidays.

Holidays Table

I want to see if today is a holiday so i enter the following:

Select case when count(HolidayKey) > 0 then 1 else 0 end as IsHoliday
 from dbo.Holidays
 where date = DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

The case statement returns the results I am looking for.   If the date is Holiday then the count of Holidaykey is greater than or equal to 1, which returns a 1.  If the date doesn’t exist in the table the count function returns a  zero.

count holidaykey

Without the count, the case statement fails to return the proper result. See below.

 

case HolidayKey

 

terrymriley61

How can one Map Census Block Data in Microsoft Excel Power Map?

The census contains a wealth of demographic data down to a very small portion of a county called a census block.  It also contains data at the zip code level as well.  Power Map can map zip codes.  Unfortunately zip codes cross county boundaries, so data can’t easily be rolled up to the county level.  It would be nice if Power Map could recognize census block data geographically.

Power Map does support Latitude and Longitude.  If we could translate the block level data to a latitude and longitude than we could map the data.  After much searching of the Census website I was able to find a source for the latitude and longitudes of the blocks based on centers of population.

If you go to the Geography Reference page on the census.gov web site you will find listed Centers of Population.

https://www.census.gov/geo/reference/centersofpop.html

Centers of Population 1

Click on Data Files and select Centers of Population by Block group.  You have to select a state.  I picked Florida.

Centers of Population 2

This will open up a text file that contains comma delimited data in the format shown below.

Centers of Population 3

We can now use this data to map the census blocks.  Save the data to a file on your hard drive.  Use the From File option in Power Query and

select either CSV or text file and load the data.  It will load without headers.

Census Block Power BI 2

Rename the columns.

Census Block Power BI 4

Add a new Column for GeoCode.   It is basically a combination of the state, county, tract and Block codes.

Census Block Power BI 5

This identifier will match the identifier used in the census data files.  Convert all columns to numbers.

Census Block Power BI 6

Close and Load this data.  Now go to insert a map.

Census Block Power Map 1

Launch Power Map.  The Data will load and recognize the latitude and longitude.

Census Block Power Map 2

Select next at the bottom right and then check population.  Tilt down to see the population.

Census Block Power Map 3

The data is now available to tie to other census data.  Below is an example where I tied in the level of education for the Duval County, Clay County, and St John County. The map is showing the number of people with college educations, bachelor degree or higher.

Census Block Power Map 4