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,110 points
93 views

1 answer to this question.

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
• 16,300 points

Related Questions In Power BI

+1 vote
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
• 31,840 points
6,977 views
0 votes
1 answer

How to rename a column using DAX in Power BI?

Hi Ramya, I found these two function that ...READ MORE

answered Aug 21 in Power BI by anonymous
• 31,840 points
302 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
• 42,330 points
101 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
• 42,330 points
109 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,110 points

edited Oct 11, 2018 by Kalgi 215 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
• 42,330 points
104 views
0 votes
2 answers

Power BI Dax Multiple IF AND Statements

HI I have Column Patient 12 13 14 15 18 IN Patient=12, 13 Out ...READ MORE

answered May 22 in Power BI by VNK
8,990 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
• 42,330 points
8,735 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
• 16,300 points
124 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
• 16,300 points
57 views