Cubeset with the condition of non-blank, noneｍpty

[DataNinja777]

Hello,

I am using Excel for monthly reporting and I'd like to write a cubeset formula which only returns non-empty result.  Please could you let me konw how I can modify the Excel formula below? 

Thank you!  

[DataNinja777 · Accepted solution]

Thank you @AmiraBedh for taking time to answer my question.  The issue was fixed by selecting the sort order key "4" instead of "6". Whatever "Alpha descending" means, it got rid of the blank value at the top.  

[v-binbinyu-msft · Accepted solution]

Hi @DataNinja777 ,

1. Identify the CubeSet Formula: Locate your current CubeSet formula which is returning both empty and non-empty results. The formula usually looks like this:

 =CUBESET("connection", "set_expression", "caption", [sort_order], [sort_by])

2. Modify Set Expression for Non-Empty Results: Adjust the "set_expression" part of your CubeSet function to include a filter that excludes empty values. This can often be done by using the `NON EMPTY` keyword within the set expression. For example:

 =CUBESET("connection", "NON EMPTY set_expression", "caption", [sort_order], [sort_by])

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AmiraBedh]

Try the following : 

=IF(CUBESETCOUNT(CUBESET("ThisWorkbookDataModel", "FILTER([Calendar].[yyyy-mm].[All].children, NOT ISEMPTY([Measure].CurrentMember))", "yyyy/mm", 6, "[Calendar].[yyyy-mm]")) > 0, CUBESET("ThisWorkbookDataModel", "FILTER([Calendar].[yyyy-mm].[All].children, NOT ISEMPTY([Measure].CurrentMember))", "yyyy/mm", 6, "[Calendar].[yyyy-mm]"), "No non-empty data")

[DataNinja777 · Accepted solution]

Thank you @AmiraBedh for taking time to answer my question.  The issue was fixed by selecting the sort order key "4" instead of "6". Whatever "Alpha descending" means, it got rid of the blank value at the top.