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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to forget data hierarchy using calculated column on matrix visual to improve its performance

The following model has five tables, four with data and the last one is used as dynamic slicer.

 

image.png

 

 

CurrentStock:

ProviderID ProductID Stock

1110
1220
2130
2240

 

 

Product:

ProductID Description

1Product 1
2Product 2

 

Provider:

ProviderID Description

1Provider 1
2Provider 2

 

Sales:

ProviderID ProductID Quantity Date

11501/01/2019
11302/02/2019
121001/01/2019
12502/02/2019
211501/01/2019
211002/02/2019
222001/01/2019
221002/02/2019

 

 

Status:

Status

High
Low
Normal

 

 

I created two four calculated measures:

 

RealStock = SUM(CurrentStock[Stock]) - SUM(Sales[Quantity])
OnlyStockProviderStatus = CALCULATE(IF([RealStock] > 9; "High"; IF([RealStock] < 4; "Low"; "Normal")); ALL('Product'[Description]))
Selected Status =
VAR
selected_value = SELECTEDVALUE('Status'[Status])
RETURN
IF(ISBLANK(selected_value); "ALL"; selected_value)
 
ShowThisStatus = IF([Selected Status] = "ALL"; "SHOW"; IF([OnlyProviderStatus] = [Selected Status]; "SHOW"; BLANK()))

 

 

I have the two following visuals:image.png

 

Since I just need to display the selected status, I created the follwing filter:

 

image.png

 

 

Now the problem occurs, since it is just a sample dataset I can not reproduce it, but on the orignal dataset I have a lot of providers and even more products. So if I filter the visual to not display blank values for ShowThisStatus, an error that my system does not have sufficient memory occurs. If I remove the product's description from the matrix it works withou problems:

 

 

image.png

 

 

Finally I want to know if it is possible to make the matrix visual "forget" its hierarchy and on only apply the filter to its root elements. If not, how can I make the filter "do not show blank values" to work?

1 ACCEPTED SOLUTION

Hi @Anonymous 

 

Please check the workaround in this article to improve the memory.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-cherch-msft
Employee
Employee

Hi @Anonymous 

 

You may try add a measure like below and use it in visual level filter.If it is not your case,please check the workaround in this article.

Measure = IF(NOT(ISBLANK([ShowThisStatus])),1)

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
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-cherch-msft , but it is not my case. The problem whem I activate the filter "Measure is not Blank" Power BI shows the memory error. 

Hi @Anonymous 

 

Please check the workaround in this article to improve the memory.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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