cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bokbob Regular Visitor
Regular Visitor

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

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.call1.PNG

 

 

call2.PNG

 

 

 

 

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

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

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

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Moderator v-sihou-msft
Moderator

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

@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,

3 REPLIES 3
Phil_Seamark Super Contributor
Super Contributor

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

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Bokbob Regular Visitor
Regular Visitor

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

Phil_Seamark

 

thanks for your quick response. 

 

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

 

 

Call3.PNG

 

 

 

 

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 ?

 

 

Moderator v-sihou-msft
Moderator

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

@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,