The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi everyone. I've spent the last couple of hours trying multiple arpproaches to this simple problem, but can't seem to work it out. I will highlight what it is I need and mention the things I've tried at the end
Here is the .pbix file and data to replicate my problem.
What I need:
In the following dashboard, I want the line plot on the right to ONLY show data for the date ranges selected in the table on the left. In other words, the [Date] column of the dji table should ONLY contain values between the [Start Service] and [End of Service] columns of the selected/highlighted rows of the table.
Figure 1
If multiple rows are selected with a non-continuous time period, I am okay with a solution that either:
What I've done so far (and failed):
dates = ADDCOLUMNS(
CALENDAR(MINX(ALLSELECTED(pres_congress),pres_congress[Start Service]),MAXX(ALLSELECTED(pres_congress),pres_congress[End of Service])),
"Year",YEAR([Date]),
"Month",MONTH([Date]),
"Day",DAY([Date]),
"DayOfWeek",WEEKDAY([Date],2)
)
Include =
IF(
(dji[Date] >= MINX(ALLSELECTED(pres_congress),pres_congress[Start Service]) && dji[Date] <= MAXX(ALLSELECTED(pres_congress),pres_congress[End of Service])),
"Include",
"Exclude"
)
In each of these cases, the resulting Table/Measure just did not update based on the selected rows.
Solved! Go to Solution.
So far, I'm able to project selected terms from pres_congres, whether contiguous or uncontiguous, to dji; but the performance of subsequent average calculation is terribly poor ... I'll leave it to you or others until I get some inspiration.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
There's a slightly easier way to this sort of thing. What you could do is write two seperate measures, BeginDate and EndDate. BeginDate will be:
So far, I'm able to project selected terms from pres_congres, whether contiguous or uncontiguous, to dji; but the performance of subsequent average calculation is terribly poor ... I'll leave it to you or others until I get some inspiration.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
This is it sir. Performance on my computer is not an issue at all. I will probably find a way to optimize this in the future, but this is the solution I was looking for. Thank you so very much.
@Anonymous , These approaches can help
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
Hi amitchandak. I went through your videos and I'm afraid I couldn't figure out how the resources will get me closer to what I want. Any chance you could use the PBIX and data files and share the solution?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
83 | |
73 | |
51 | |
42 |
User | Count |
---|---|
140 | |
112 | |
72 | |
64 | |
63 |