The following model has five tables, four with data and the last one is used as dynamic slicer.
ProviderID ProductID Stock
ProviderID ProductID Quantity Date
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:
Since I just need to display the selected status, I created the follwing filter:
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:
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?
Solved! Go to Solution.
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)
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.