fact and dimention table hirarchy in power bi report for drill down option

[Bokbob]

HI All, i have fact and dimention table as per below.

where there are state column as well as StateProvinceCode columns in two tables.

they have a relation between two tables with GeographyKey.

now i would like to show on graph with State related values and with drill down i would like to show LABEL on the axis as--

State(Fact call table)-StateProvinceCode(from Geography dimention table) 

Ex:

TEXAS in normal graph showing otehr values on other axis)

with drllldown: TEXAS(facttable state)-TEXAS EAST (dimentiontable stateProvinceCode) (---as a sample)

Please help.. i hope i explained myself well

[Phil_Seamark · Accepted solution]

Hi @Bokbob

It looks like you have everything you need.  I suggest you use columns from your Dimension table on your Axis rather than columns from your fact table (although this should work).

You can create hierarchies by simply adding other columns to your Axis.  Not all visuals support drilldowns, but those that do will let you.

[Phil_Seamark · Accepted solution]

Hi @Bokbob

It looks like you have everything you need.  I suggest you use columns from your Dimension table on your Axis rather than columns from your fact table (although this should work).

You can create hierarchies by simply adding other columns to your Axis.  Not all visuals support drilldowns, but those that do will let you.

[Bokbob]

Phil_Seamark

thanks for your quick response. 

the graph is mainly for calls and where as my y axis is showing states.

and upon drill down i wanted to see someting like TEXAS-TEXAS EAST

so if i grab both state and stateProvinceCode from same table i wonder if it works fine for showing call counts as per normal ?

and a question: so cant crate hirarchies from one table to another ?

[v-sihou-msft · Accepted solution]

@Bokbob

As @Phil_Seamark suggested, it's better to include all information related to State in your DimGeography table. And your fact table connects DimGeography with Geography key. So just add the State column into DimGeography table and create a hierarchy.

It's not supported inlcude fields from different dataset into same hierarchy.

Regards,