Dax query in power bi

0 votes

So i have a user name, start-date, end-date and the number of working hours.

User     Start-Date    End-DATE     Hour
Priyaj    2015-02-05    2015-02-08   32

I have to assign working hours value each day. How do I do that using dax?

Oct 22, 2018 in Power BI by lina
• 8,100 points
45 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
0 votes

Here is my table before I have made any changes

User        Start        End           Hour
UserA       01/01/2018   05/01/2018    32

Create a custom column to calculate the number of dates between [Start] and [End]

Dates = { Number.From([Start]..Number.From([End]) }

Create another custom column to calculate the number of [Hour]s divided by the number of list items. Do not expand the list before this step!

CountPerDay = [Hour] / List.Count([Dates])

Finally expand your list column to show the number of hours required per day. Note how the dates are in a numeric format. Changing the column data type to "Date" or "DateTime" will change these back to the correct value.

Using my example you should now have a table that looks something like the below:

User       Start         End          Hour     Dates         CountPerDay
UserA      01/01/2018    05/01/2018   32       01/01/2018    6.4
UserA      01/01/2018    05/01/2018   32       02/01/2018    6.4
UserA      01/01/2018    05/01/2018   32       03/01/2018    6.4
UserA      01/01/2018    05/01/2018   32       04/01/2018    6.4
UserA      01/01/2018    05/01/2018   32       05/01/2018    6.4     

If I add the UserB into the mix with the following records:

User        Start        End           Hour
UserB       01/02/2018   02/02/2018    10

The table updates as follows:

User       Start         End          Hour     Dates         CountPerDay
UserA      01/01/2018    05/01/2018   32       01/01/2018    6.4
UserA      01/01/2018    05/01/2018   32       02/01/2018    6.4
UserA      01/01/2018    05/01/2018   32       03/01/2018    6.4
UserA      01/01/2018    05/01/2018   32       04/01/2018    6.4
UserA      01/01/2018    05/01/2018   32       05/01/2018    6.4
UserB      01/02/2018    02/02/2018   10       01/02/2018    5
UserB      01/02/2018    02/02/2018   10       02/02/2018    5  
answered Oct 22, 2018 by Hannah
• 14,080 points

Related Questions In Power BI

0 votes
1 answer

How to replace null values with custom values in Power BI(power query editor)?

Hi Nithin, To fill or replace the null or ...READ MORE

answered Mar 8 in Power BI by CHERUKURI
• 13,300 points
283 views
0 votes
1 answer

Programmatically sign in - power bi

Basically: POST request to: https://login.microsoftonline.com/common/oauth2/token Body, form-url-encoded: grant_type: "password" scope: "openid" resource: "https://analysis.windows.net/powerbi/api" client_id: ...READ MORE

answered Sep 19, 2018 in Power BI by Kalgi
• 35,750 points
35 views
0 votes
1 answer

create interactive R plots in Power BI

 Right now Power BI only supports render ...READ MORE

answered Sep 20, 2018 in Power BI by Kalgi
• 35,750 points
23 views
0 votes
1 answer

Is there a way to convert JSON data to readable table in power bi

Follow these steps step 1 - Click on ...READ MORE

answered Sep 24, 2018 in Power BI by Hannah
• 14,080 points
1,093 views
+3 votes
2 answers

Combine tables in Power BI

You can also achieve this using a ...READ MORE

answered Oct 5, 2018 in Power BI by lina
• 8,100 points

edited Oct 11, 2018 by Kalgi 85 views
0 votes
1 answer

Power Bi Dax Table

You need to wrap the numbers in ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 35,750 points
41 views
0 votes
1 answer

Power BI Dax Multiple IF AND Statements

I suspect your issue is that alphabetically ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 35,750 points
3,589 views
0 votes
1 answer

Distinct count filtered by condition using Power BI Dax

Try this, it should work: DistinctCountActiveMonths = CALCULATE( ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 35,750 points
1,960 views
0 votes
1 answer

list reports with calculated percentage in Power BI using dax

Create a Measure called Total Revenue: Total Revenue = SUMX( ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 14,080 points
51 views
0 votes
1 answer

How many reports can be created in one Power BI reports

You have a limit of 1000 datasets ...READ MORE

answered Sep 24, 2018 in Power BI by Hannah
• 14,080 points
20 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.