Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
hi all,
In the following visual, at the extreme left I have item heirarchy (Assembly group>item material group>items)
Then I have 2 columns which show measure values -
Issue - at someplaces at higher level of heirarchy, I am not getting the value of the second meausre as shown in screenshot
Formula for the measures -
ATP Lead time =
var filtertable = FILTER(node_table,node_table[version_date] = MAX(node_table[version_date]))
return
CALCULATE(AVERAGE(node_table[quantity]),filtertable)
// this measure calculates takes the max version date and then do the average of quantity with whatever data it finds for max version date
ATP Lead time previous week =
var weekcheck = MAX(node_table[weekcolumn]) -1
VAR filterTable2 = FILTER(ALL('node_table'),'node_table'[weekcolumn] = weekCheck && 'node_table'[item name] = SELECTEDVALUE('node_table'[item name]))
var lastWeekVersionDate = CALCULATE(MAX('node_table'[version_date]),filterTable2)
VAR lastWeekATPleadTime = CALCULATE(AVERAGE('node_table'[quantity]), FILTER(filterTable2, 'node_table'[version_date] = lastWeekVersionDate))
return lastWeekATPleadTime
// this measure calculates takes the max version date of previous week and then do the average of quantity with whatever data it finds for max version date of previous week. this part - node_table'[item name] = SELECTEDVALUE('node_table'[item name])) is additonal and without it the code was breaking
Also we have item and assembly group filter in the report. Can someone help on this issue?
let me know, if more inputs are required from my side
@Anonymous did you ever manage to get this to work? Having the same issue here.
Hi @JayRooster ,
I used the combination of Isinscope and switch statement and it worked -
var finalAnswer = ......
SWITCH (
TRUE,
ISINSCOPE ( 'KPI dim_items'[item_name]),blank(),
ISINSCOPE( 'KPI dim_items'[item material group new]), blank(),
ISINSCOPE ( 'KPI dim_items'[AG]),standardWeek
)
return finalAnswer
Thanks for getting back to me! Will give it a try!
Hi, @Anonymous
According to your sample picture, I think you can solve this problem using the ISINSCOPE() function in DAX, you can try to add a new measure like this:
ATP Lead time previous week1 =
IF (
ISINSCOPE ( 'Table'[Assembly Group] ),
SUMX ( 'Table', [ATP Lead time previous week] ),
[ATP Lead time previous week]
)
Then you can go to the matrix to replace the original measure [ATP Lead time previous week] with this new measure to check if it can work.
More info about the ISINSCOPE() function in DAX
If you still have a problem, you can post some sample data or your test pbix file so that we can help you in advance.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi @v-robertq-msft, thanks for trying out the problem.
Inscope function is only working when I need to hide the values at different values of the heirarchy.
But in this use case it is not working.
I will try to work with my team and get you the desired data or .pbix by tomorrow.
Thanks
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
127 | |
108 | |
87 | |
70 | |
66 |