Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have the following data:
I am able to filter out by each year and period and the correct YTD figure displays thanks to help from Power BI Superusers. I now want to display a line chart which will display the previous figures depending on the user selection:
However, whenever I select Period 5 for example it only displays Period 5 and doesn't display Periods 1-5 which is what I want it to do. So the data for the line chart should display Period 1 to Period X, and X being the user selection.
Please note that the Year and Period slicers are already related to the Period and YTD tables, that's how I've managed to get the right figures for Period and YTD.
Any help is greatly appreciated.
Many thanks to Greg_Deckler who has provided the following solution:
SIP Actual Line Chart = VAR __Year = MAX('SIP_Activities'[Year]) VAR __Period = MAX('SIP_Activities'[Period]) + 0 VAR __LinePeriod = MAX('Periods'[Period]) + 0 VAR __Table = FILTER(ALL(SIP_Activities),[Year] = __Year && [Period]+0 <= __Period) VAR __Table1 = FILTER(__Table,[Period]+0 = __LinePeriod) RETURN SUMX(__Table1,[Actual-LD]) + SUMX(__Table1,[Late Delivery])
If you want to apply it to other tables, columns, etc. then he advised with the following:
The variable names can stay the same, the overall name of the measure needs to be unique
SIP Actual Line Chart =
VAR __Year = MAX('SIP_Activities'[Year])
VAR __Period = MAX('SIP_Activities'[Period]) + 0
VAR __LinePeriod = MAX('Periods'[Period]) + 0
VAR __Table = FILTER(ALL(SIP_Activities),[Year] = __Year && [Period]+0 <= __Period)
VAR __Table1 = FILTER(__Table,[Period]+0 = __LinePeriod)
RETURN
SUMX(__Table1,[Actual-LD]) + SUMX(__Table1,[Late Delivery])
Red is the measure name, has to be unique. Green are table names that you would update. Blue is column names that you would update. You could leave the measure names the same although the names may not make total sense depending on your tables/periods, etc. that you are switching things to.
Hi @PowerBI-Newbie ,
What you need is an unrelated slicer.
Create an unrelated table and use it as slicers.
Table 2 = SELECTCOLUMNS('Table',"year",'Table'[Year],"period",'Table'[Period])
Then create a measure to apply to the visual level filter.
Measure = IF(MAX('Table'[Year])=SELECTEDVALUE('Table 2'[year])&&MAX('Table'[Period])<=SELECTEDVALUE('Table 2'[period])&&MAX('Table'[Period])>=1,1)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @V-lianl-msft , thank you for your reply.
I've already got slicers related to data tables that calculate other figures for me, how would it work if I create the same slicers again, it'll confuse my users. Any advise on this.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |