Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Values not showing up in matrix visual at higher hierarchy

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 - 

rohit1410_0-1621579341356.png

 

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

6 REPLIES 6
JayRooster
Frequent Visitor

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

Anonymous
Not applicable

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

rohit1410_0-1676632088523.png

 

 

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

 

v-robertq-msft
Community Support
Community Support

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
Not applicable

Thanks @v-robertq-msft 

Anonymous
Not applicable

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

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.