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 a requirement to display previous 30 values of the dimension based on the slicer selection and corresponding measures in a bar chart. From below example data, if the user slectes MI040 in slicer then bar chart should display values from MI010 to MI040 along with corresponding measure values. Can someone please help with this?, Many thanks !..Cheers
ShipmentNo M1 M2
MI001 10 10
MI002 12 14
MI003 30 34
MI004 40 44
------
------
MI040 200 220
Solved! Go to Solution.
Hi @kumsha1 ,
You can create a custom column in query editor to get number value in your ShipmentNo column:
Table.TransformColumnTypes(Table.AddColumn(#"Renamed Columns", "No", each Text.Select([ShipmentNo], {"0".."9"})),{{"No", Int64.Type}})
Then use the following measure in your bar chart.
Measure = IF(MAX('Table'[No])<=SELECTEDVALUE('Table'[No]) && MAX('Table'[No])>=SELECTEDVALUE('Table'[No])-30,CALCULATE([Your Measure],ALL('Table')),Blank())
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @kumsha1 ,
You can create a custom column in query editor to get number value in your ShipmentNo column:
Table.TransformColumnTypes(Table.AddColumn(#"Renamed Columns", "No", each Text.Select([ShipmentNo], {"0".."9"})),{{"No", Int64.Type}})
Then use the following measure in your bar chart.
Measure = IF(MAX('Table'[No])<=SELECTEDVALUE('Table'[No]) && MAX('Table'[No])>=SELECTEDVALUE('Table'[No])-30,CALCULATE([Your Measure],ALL('Table')),Blank())
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Thanks for your reply, i was trying in the same direction and created a column for Shipment Number # but the measure is not changing the graph X-axis values?. Ex: if the user selected 40 in the report slicer then bar chart X-axis should only display values from 10....40 and corresponding measure values, i.e. Previous 30 Shipment Numbers # based on the Slicer selection...
Hi @kumsha1 ,
You'd better create a new table with Shipment Number column and use it as slicer .
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@kumsha1 , You have create a Rank on shipment no
Column Rank
Ship rank =rank(All(Table),Table[[ShipmentNo],asc,dense )
Meausure =
var _max =maxx(allselected(Table),Table[rank])
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(Table),Table[rank]<=_max && Table[rank]>=_min ))
but you might need to have an independent table to display shipment no
Like this example :https://www.youtube.com/watch?v=duMSovyosXE
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 |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |