cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Need help: Matrix table

Dear,

 Style no. ATTRIBUTE Store 1 Store 2 Store 3 Store 4 Store 5 Store 6 NU529 DGRY 1 3 4 8 2 0 NU529 NAV 2 5 0 3 9 4 NU529 RED 3 12 8 5 4 2 P209 MAR 5 2 7 6 0 3 P209 BLK 1 2 3 3 6 1

I want a matrix table like the above.
1. I need to filter one store, (for quantity =<5) it should only display those styles. BUT
2. The other stores should display all the quantity they have on those styles displayed.

I hope you have solution for this.
Thanks in advance for your kind cooperation.

1 ACCEPTED SOLUTION
Frequent Visitor

hi,
thanks for your support. I think i did not explained it clearly.

Here you find the screen shot of my data tables.
REQUEST: Once again, my requirement is to filter any store
And the filtered store should show only the  Styles and attribute(color)

Based on:   on hand qty to be <=5, and sales >= 5.

The others stores should show their quantity on hand, for the styles

Every time to change the stores in the measure will be very difficult, can we use slicers, filters to select.

4 REPLIES 4
Frequent Visitor
Super User

Hi,

Clearly show the result you are expecting.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi @shakeelkhan ,

My understanding of 'all quantities' is that values of the same style are added together, regardless of attributes.

All the quantity for store1 = 6,All the quantity for store2 = 6

The Table data is shown below:

Let's assume that the store we are filtering for is 'Store 3'

1. Use the following DAX expression to create a table

``````Table 2 =
SUMMARIZE (
'Table',
'Table'[Style no.],
"Store 1", SUM ( 'Table'[Store 1] ),
"Store 2", SUM ( 'Table'[Store 2 ] ),
"Store 3", SUM ( 'Table'[Store 3] ),
"Store 4", SUM ( 'Table'[Store 4] ),
"Store 5", SUM ( 'Table'[Store 5] ),
"Store 6", SUM ( 'Table'[Store 6] )
)
``````

2.Use the following DAX expression to create a table

``````Table 3 =
FILTER ( 'Table', 'Table'[Store 3] <= 5 ),
"all_for_store1", LOOKUPVALUE ( 'Table 2'[Store 1], 'Table 2'[Style no.], 'Table'[Style no.] ),
"all_for_store2", LOOKUPVALUE ( 'Table 2'[Store 2], 'Table 2'[Style no.], 'Table'[Style no.] )
)
``````

3.Use the following DAX expression to create a measure

``_Store 1 = IF(ISINSCOPE('Table 3'[ATTRIBUTE]),BLANK(),MAX('Table 3'[all_for_store1]))``

4.Use the following DAX expression to create a measure

``_Store 2 = IF(ISINSCOPE('Table 3'[ATTRIBUTE]),BLANK(),MAX('Table 3'[all_for_store2]))``

5.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

hi,
thanks for your support. I think i did not explained it clearly.

Here you find the screen shot of my data tables.
REQUEST: Once again, my requirement is to filter any store
And the filtered store should show only the  Styles and attribute(color)

Based on:   on hand qty to be <=5, and sales >= 5.

The others stores should show their quantity on hand, for the styles

Every time to change the stores in the measure will be very difficult, can we use slicers, filters to select.