Power BI Time-based drill downs powered by Azure Data Warehouse

0 votes

I have designed a simple Azure Data Warehouse where I want to track stock of my products on a periodic basis. Moreover, I want to have the ability to see that data grouped by month, weeks, days and hours with the ability to drill down from top to bottom. I have defined 3 dimensions:

DimDate
DimTime 
DimProduct

I have also defined a Fact table to track product stocks:

FactStocks
- DateKey (20160510, 20160511, etc)
- TimeKey (0..23)
- ProductKey (Product1, Product2)
- StockValue (number, 1..9999)

My fact sample data is below:

20160510 20 Product1 100
20160510 20 Product2 30
20160510 21 Product1 110
20160510 21 Product2 35
20160510 22 Product1 112
20160510 22 Product2 28
20160510 23 Product1 120
20160510 23 Product2 31
20160511 00 Product1 150
20160511 00 Product2 29
20160511 01 Product1 95
20160511 01 Product2 40

What I need is a chart of product availability over time with ability to check total (where x axis represents hours), as well as ability to filter by specific product:

Total - 130, 145, 140, 151, 179, 135
Product1 - 100, 110, 112, 120, 150, 95;
Product2 - 30, 35, 28, 31, 29, 40;

x-> 20,21,22,23,00,01

Moreover I need an ability to drill up and browse average availability by days and products (where x axis represents days, weeks, month, years available via the DimDate table):

Total - 141.5, 157
Product1 - 110.5, 122.5
Product2 - 31, 34.5

x-> 20160510, 20160511

It seems that Power BI is unable to make that group by date thing, because it's trying to use an aggregate function to get the daily value and there is no ability to specify the average function which will take into account products (with grouping by product). The aggregating function just doesn't work here, Power BI is trying to sum all values across all products for a given day and get the average (e.g. for 20160511):

150+29+95+40 / 4 = 78.5

When what I need is the following:

(150+29) + (95+40) / 2 = 157

I just want to make it interactive with an ability to chose a product and easily drill up and down. Please advise how I should modify my Warehouse structure to support my scenario.

Oct 29, 2018 in Power BI by Upasana
• 8,620 points
797 views

1 answer to this question.

0 votes

I don't really think you need to change the structure. If it were me, I'd have created a New Measure on your FactStocks table (using Power BI Desktop), to calculate Availability as you require it using DAX functions.

I'm guessing a bit as you haven't really put out your requirement besides 1 example, but it would probably look something like this:

Availability = SUM([StockValue])/DISTINCTCOUNT([ProductKey])
answered Oct 29, 2018 by Shubham
• 13,490 points

Related Questions In Power BI

0 votes
1 answer

Switch Power BI data sources from Excel to Azure analysis services

One option that you should consider would ...READ MORE

answered Sep 28, 2018 in Power BI by Kalgi
• 52,360 points
650 views
+1 vote
2 answers

403 error thrown by power bi report server when trying to get list of data sets

Hey @saranya, Try removing the header and ...READ MORE

answered Oct 29, 2018 in Power BI by Kalgi
• 52,360 points
4,898 views
0 votes
1 answer

How to create a new slicer to filter data by district manager in Power BI?

Follow the steps Open power bi  desktop nd ...READ MORE

answered Oct 10, 2023 in Power BI by Monika kale
919 views
0 votes
1 answer

Power BI - Azure SQL Warehouse

Hi, Follow below steps: 1. Go to Data source. 2. Go ...READ MORE

answered Mar 25, 2019 in Power BI by Cherukuri
• 33,030 points
805 views
0 votes
1 answer

The file exists before processing with hadoop command

Took session and it got resolved. READ MORE

answered Dec 18, 2017 in Big Data Hadoop by Sudhir
• 1,610 points
1,003 views
0 votes
1 answer

How to sync Hadoop configuration files to multiple nodes?

For syncing Hadoop configuration files, you have ...READ MORE

answered Jun 21, 2018 in Big Data Hadoop by HackTheCode
1,311 views
0 votes
10 answers

What is the difference between Mongodb and Hadoop?

MongoDB is a NoSQL database, whereas Hadoop is ...READ MORE

answered Jun 20, 2018 in Big Data Hadoop by jenny_code
11,993 views
0 votes
1 answer

Power BI : Creating time factors

Go to Edit Queries window of Power BI Desktop. ...READ MORE

answered Nov 30, 2018 in Power BI by Shubham
• 13,490 points
928 views
0 votes
1 answer

Calling Azure API using Power BI with Next Link

Try this let iterations = ...READ MORE

answered Apr 9, 2019 in Power BI by Shubham
• 13,490 points
1,834 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP