Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have the following two tables, along with a Calender Table consisting of 30 calender dates (starting from 11/3/22 to 4/4/22)) with day no. (1,2,3,4,5). I have already created relationship between start date and Calender date columns. Now when i use a visual bar chart with "calender day no. " on x axis and "manpower" on Y axis.
In this case, the sum of manpower is displayed only on dates corresponding to start date only(as start date is linked with calendar date).
What I want is that the sum of manpower is displayed for the entire duration. for example If Team "MA" is working for a duration of 15 days,( row 1), then sum of manpowr of "MA" should reflect on day 1 (11/3/22) to day 15 (25/3/22)
Fact table
SR# Dep Team Start Date End Date
1 | MMD | MA | 11/03/2022 7:00 | 25/03/2022 8:00 |
2 | MMD | MA | 25/03/2022 10:00 | 30/03/2022 19:00 |
1 | EMD | EA | 14/03/2022 13:00 | 14/03/2022 15:00 |
2 | EMD | EB | 14/03/2022 13:00 | 14/03/2022 15:00 |
both are linked with "team" column.
Dimension table
Team | Manpower Type | Manpower |
MA | Milwright | 2 |
MA | Fitter | 4 |
MA | Helper | 6 |
MA | Scaffolder | 4 |
MA | Rigger | 1 |
MA | SBM | 3 |
MA | FME | 1 |
MA | Fabricator | 0 |
MA | Insulator | 0 |
MA | Technician | 0 |
EA | Milwright | 0 |
EA | Fitter | 0 |
EA | Helper | 0 |
EA | Scaffolder | 0 |
EA | Rigger | 0 |
EA | SBM | 0 |
EA | FME | 0 |
EA | Fabricator | 0 |
EA | Insulator | 0 |
EA | Technician | 15 |
EB | Milwright | 0 |
EB | Fitter | 0 |
EB | Helper | 0 |
EB | Scaffolder | 0 |
EB | Rigger | 0 |
EB | SBM | 0 |
EB | FME | 0 |
EB | Fabricator | 0 |
EB | Insulator | 0 |
EB | Technician | 10 |
Solved! Go to Solution.
@sohaibnomani use the following measure
Measure =
SUMX (
SUMMARIZE (
ADDCOLUMNS (
FILTER (
CROSSJOIN ( 'calendar', _fact ),
_fact[Start Date] <= 'calendar'[Date]
&& _fact[End Date] >= 'calendar'[Date]
),
"sum",
CALCULATE (
SUM ( _dimension[Manpower] ),
TREATAS ( { CALCULATE ( MAX ( _fact[Team] ) ) }, _dimension[Team] )
)
),
[Date],
[Team],
[sum]
),
[sum]
)
@sohaibnomani do you mean this?
_totalManpower =
VAR _team =
MAX ( _fact[Team] )
VAR _start =
MAX ( _fact[Start Date] )
VAR _end =
MAX ( _fact[End Date] )
VAR _tbl =
FILTER (
CROSSJOIN ( 'calendar', _fact ),
_start <= 'calendar'[Date]
&& _end >= 'calendar'[Date]
)
RETURN
SUMX (
ADDCOLUMNS (
_tbl,
"_sum",
CALCULATE (
SUM ( _dimension[Manpower] ),
TREATAS ( { _team }, _dimension[Team] )
)
),
[_sum]
)
Thanks for your solution. However slight changes would be required in the code. In ur file, i have ploted _totalmanpower MEASURE against Calendar Date. I m getting results below. What is required is that the chart on x axis plotes all dates and show the sum of manpower on each date .I would be using dates manpower basically for the plot in order to see total manpower bar graph. A team filter would be applied later
@sohaibnomani What is required is that the chart on x axis plotes all dates and show the sum of manpower on each date - The given dataset does not have Manpower by Date anywhere in the data? Can you revise your data?
The fact table contains start date and end date. All the dates in between would have the same team (MA, EA, EB) while manpower is mentioned in the dimension table.
@sohaibnomani based on the dataset that you provided what was the output you desired?
this result. on 14th EA and EB teams are also working so total count is 46
Date | Manpower |
11/03/2021 | 21 |
12/03/2021 | 21 |
13/03/2021 | 21 |
14/03/2021 | 46 |
15/03/2021 | 21 |
16/03/2021 | 21 |
17/03/2021 | 21 |
18/03/2021 | 21 |
19/03/2021 | 21 |
20/03/2021 | 21 |
21/03/2021 | 21 |
22/03/2021 | 21 |
23/03/2021 | 21 |
24/03/2021 | 21 |
25/03/2021 | 21 |
@sohaibnomani use the following measure
Measure =
SUMX (
SUMMARIZE (
ADDCOLUMNS (
FILTER (
CROSSJOIN ( 'calendar', _fact ),
_fact[Start Date] <= 'calendar'[Date]
&& _fact[End Date] >= 'calendar'[Date]
),
"sum",
CALCULATE (
SUM ( _dimension[Manpower] ),
TREATAS ( { CALCULATE ( MAX ( _fact[Team] ) ) }, _dimension[Team] )
)
),
[Date],
[Team],
[sum]
),
[sum]
)
One thing that I missed to mension. If in the above fact table we have another entery of EA team doing two activities in a single day but different time, then the measure should count it once, rather than twice. Meanwhile i will try ur existing solution. THanks for your swift replies.
1 | EMD | EA | 14/03/2022 09:00 | 14/03/2022 13:00 |
2 | EMD | EA | 14/03/2022 13:00 | 14/03/2022 17:00 |
This is i am getting, I don't know what mistake i am doing. getting same results every time.
@sohaibnomani as you can see the data that you provided and the code that I wrote return exactly you had as the picture.
I am unable to select "startdate" column in expression secion of "filter" command. Please guide.
@sohaibnomani why do you want to select Start Date? follow the attached pbix
Hi,
Here is one way to do this:
1. Create a new column to your fact table which displays teams manpower:
2. Create following measure:
The results check out with the data.
I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!
Proud to be a Super User!
Following is the result, which is same as before
Applied formula for column
for measure
I have linked date column from calendar table to startdateonly from the Fact table, but when select "startdateonly" in the above visual chart, no data is displayed while with "day" of Calendar table, it works.
Do you need the relationship between start date and calendar for some other reason? If not you could disable it. That is likely causing the issue here.
Proud to be a Super User!
User | Count |
---|---|
42 | |
27 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |