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 created few DAXs and it was working fine, but i got change in the requriment which i'm not able to do
Old Requirement when selected Year = 2021, Month = March as slicer values then my graph shows as below
1.) Bar measure show selected Month
2.) Line measure show all the months
I have 4 Bar measures, I have 4 line measures for reference im sharing of one line measure which i'm having issue in line measure.
Line measure DAX created as below
Line Measure =
var _tab=ALL('Sheet1'[Month])
var _tab1=ALLSELECTED('Sheet1'[Year])
RETURN
CALCULATE(SUM('Sheet1'[ActualData]),
FILTER(('Sheet1'),
'Sheet1'[month] in _tab &&
'Sheet1'[Year] in _tab1
))
Now new requirement, when selected Year = 2021, Month = March as slicer values then it should show as below
1.) Bar measure Should show selected Month ( Same as old Requirment)
2.) Line measure Should show upto selected Month ( New Requirment )
so how to alter or modify existing logic for line measure please help
Please find the below sample data for understanding columns ( I have Year, MonthNum, Month )
Year | MonthNum | Month | ActualData | ActualTarget |
2021 | 4 | April | 111.72 | 113 |
2021 | 5 | May | 106.54 | 113 |
2021 | 4 | April | 118.07 | 113 |
2021 | 5 | May | 117.65 | 113 |
2021 | 4 | April | 115.1 | 113 |
2021 | 5 | May | 83.86 | 113 |
2021 | 4 | April | 118.52 | 113 |
2021 | 5 | May | 104.5 | 113 |
2021 | 4 | April | 105.83 | 113 |
2021 | 5 | May | 93.06 | 113 |
2021 | 4 | April | 119.56 | 113 |
2021 | 5 | May | 118.85 | 113 |
2021 | 4 | April | 119.8 | 113 |
2021 | 5 | May | 60 | 113 |
Thanks,
Solved! Go to Solution.
Hi @ramshoney1225 ,
According to your description, you have a slicer of month column, and the axis of the chart is also month, then when you select a value in the slicer, how can not the chart axis be filtered.
My solution is create another new table.
Table = SUMMARIZE('Sheet1','Sheet1'[Month],'Sheet1'[MonthNum])
The formula of Line Measure is:
Line Measure =
VAR _tab =
ALLSELECTED ( 'Sheet1'[Year] )
RETURN
CALCULATE (
SUM ( 'Sheet1'[ActualData] ),
FILTER (
( 'Sheet1' ),
'Sheet1'[MonthNum] <= SELECTEDVALUE ( 'Table'[MonthNum] )
&& 'Sheet1'[Year] IN _tab
)
)
Then put the Month column in the new table in a slicer, the original month column in the chart axis, the Line measure will show upto selected Month.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @ramshoney1225 ,
According to your description, you have a slicer of month column, and the axis of the chart is also month, then when you select a value in the slicer, how can not the chart axis be filtered.
My solution is create another new table.
Table = SUMMARIZE('Sheet1','Sheet1'[Month],'Sheet1'[MonthNum])
The formula of Line Measure is:
Line Measure =
VAR _tab =
ALLSELECTED ( 'Sheet1'[Year] )
RETURN
CALCULATE (
SUM ( 'Sheet1'[ActualData] ),
FILTER (
( 'Sheet1' ),
'Sheet1'[MonthNum] <= SELECTEDVALUE ( 'Table'[MonthNum] )
&& 'Sheet1'[Year] IN _tab
)
)
Then put the Month column in the new table in a slicer, the original month column in the chart axis, the Line measure will show upto selected Month.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@ramshoney1225 , best is that you use before date range slicer. Other wise you need an independent date table for slicer
You need one or more measures like
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])5
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] <=_max))
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |