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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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