Values not showing up in matrix visual at higher hierarchy

[Anonymous]

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

[JayRooster]

@Anonymous did you ever manage to get this to work? Having the same issue here. 

[Anonymous]

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

[JayRooster]

Thanks for getting back to me! Will give it a try!

[v-robertq-msft]

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.

[Anonymous]

Thanks @v-robertq-msft 

[Anonymous]

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