Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello, I'm having trouble coming up with the following task. I have a calendar table created in Power BI, and it is connected to another two tables, the first contains the number of working days in each month and the second is a fact table. I need to create a line chart, where the months' names are displayed on the x-axis and the values are calculated by dividing the number of items from the fact table by the number of working days from this table. However, I also want to enable a filter on the page based on the months, so that when the user selects March, for example, the cumulative values up to that chosen month will be displayed, resulting in the graph showing January, February, and March. How can I have connected tables and simultaneously filter cumulative values by month without filtering only the selected one?
Solved! Go to Solution.
Hi @hruv01 ,
I created some data:
Month_Worknumber:
Items:
Relationships in tables:
Here are the steps you can follow:
1. Create measure.
Value =
var _items=
SUMX(
FILTER(ALLSELECTED('items'),
YEAR('items'[Date])=YEAR(MAX('items'[Date]))&&
MONTH('items'[Date])=MONTH(MAX('items'[Date]))),[Projectnumber])
var _work=
SUMX(
FILTER(ALLSELECTED('Month_Worknumber'),
YEAR('Month_Worknumber'[Date])=YEAR(MAX('Month_Worknumber'[Date]))&&
MONTH('Month_Worknumber'[Date])=MONTH(MAX('Month_Worknumber'[Date]))),[Worknumber])
return
_items / _work
Flag =
var _year=SELECTEDVALUE('calendar table'[Year])
var _month=SELECTEDVALUE('calendar table'[Month])
return
IF(
YEAR(MAX('Month_Worknumber'[Date]))=_year &&MONTH(MAX('Month_Worknumber'[Date]))<=_month,1,0)
Measure =
SUMX(
FILTER(ALLSELECTED(Month_Worknumber),
YEAR('Month_Worknumber'[Date])=YEAR(MAX('Month_Worknumber'[Date]))&&
MONTH('Month_Worknumber'[Date])<=MONTH(MAX('Month_Worknumber'[Date]))),[Value])
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @hruv01 ,
I created some data:
Month_Worknumber:
Items:
Relationships in tables:
Here are the steps you can follow:
1. Create measure.
Value =
var _items=
SUMX(
FILTER(ALLSELECTED('items'),
YEAR('items'[Date])=YEAR(MAX('items'[Date]))&&
MONTH('items'[Date])=MONTH(MAX('items'[Date]))),[Projectnumber])
var _work=
SUMX(
FILTER(ALLSELECTED('Month_Worknumber'),
YEAR('Month_Worknumber'[Date])=YEAR(MAX('Month_Worknumber'[Date]))&&
MONTH('Month_Worknumber'[Date])=MONTH(MAX('Month_Worknumber'[Date]))),[Worknumber])
return
_items / _work
Flag =
var _year=SELECTEDVALUE('calendar table'[Year])
var _month=SELECTEDVALUE('calendar table'[Month])
return
IF(
YEAR(MAX('Month_Worknumber'[Date]))=_year &&MONTH(MAX('Month_Worknumber'[Date]))<=_month,1,0)
Measure =
SUMX(
FILTER(ALLSELECTED(Month_Worknumber),
YEAR('Month_Worknumber'[Date])=YEAR(MAX('Month_Worknumber'[Date]))&&
MONTH('Month_Worknumber'[Date])<=MONTH(MAX('Month_Worknumber'[Date]))),[Value])
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
the first contains the number of working days in each month
Eliminate that table and add a "working day" flag in your calendar table.
User | Count |
---|---|
89 | |
75 | |
69 | |
65 | |
58 |
User | Count |
---|---|
103 | |
94 | |
74 | |
60 | |
59 |