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,220 points
1,031 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,540 points

Related Questions In Power BI

+2 votes
3 answers

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
• 33,030 points
159,379 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 22, 2019 in Power BI by anonymous
• 33,030 points

edited Dec 31, 2021 by Soumya 36,673 views
0 votes
3 answers

How to add an extra column to the existing table in power bi query editor?

Click on edit queries after loading source ...READ MORE

answered Dec 16, 2020 in Power BI by Roshni
• 10,520 points
78,777 views
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, 2020 in Power BI by Jitesh
19,126 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,220 points

edited Oct 11, 2018 by Kalgi 2,421 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
• 52,350 points
1,137 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 23, 2019 in Power BI by VNK
18,894 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
• 52,350 points
37,590 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,540 points
1,587 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,540 points
1,628 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