cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
caaarlos Member
Member

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

Accepted Solutions
Community Support Team
Community Support Team

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

Hi @caaarlos 

 

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.
3 REPLIES 3
Community Support Team
Community Support Team

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

Hi @caaarlos 

 

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.
caaarlos Member
Member

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

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. 

Community Support Team
Community Support Team

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

Hi @caaarlos 

 

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.