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,200 points
191 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
• 18,070 points

Related Questions In Power BI

+3 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, 2019 in Power BI by Cherukuri
• 32,460 points
29,792 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, 2019 in Power BI by anonymous
• 32,460 points
4,378 views
0 votes
1 answer
0 votes
1 answer

How to demote Headers in Power BI query editor?

Hi, There are the steps which you can ...READ MORE

answered Feb 3 in Power BI by Jitesh
2,718 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,200 points

edited Oct 11, 2018 by Kalgi 558 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
• 51,890 points
233 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, 2019 in Power BI by VNK
14,417 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
• 51,890 points
20,784 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
• 18,070 points
276 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
• 18,070 points
111 views