Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
How do I find only the rows in MDX where I have multiple columns and I need to remove those which are "useless". "Useless is defined as BOTH of the columns being either null or 0?
For instance, of the following:
... Measure 1 Measure2 1 0 1 null 0 1 null 1 null 0 0 0 null null 0 null
I only want the first 4 rows because they have value (other than 0) in one of the columns, whereas the last 4 I don't want because they have no value or 0 as value.
So far, I tried the following, using nonempty
SELECT { [Measures].[Measure1] , [Measures].[Measure2] } ON COLUMNS, NONEMPTY ( CROSSJOIN ( [Date].[Date] , [Some].[...] , [Other].[...] , [Dimensions].[...] ) , { [Measures].[Measure1] } ) ON ROWS FROM ...
but that doesn't give me what I want, and only filters on one of the measures. I guess I should put an EXCEPT() for .&[0] somewhere, but I'm unsure of where and how to interact with the nonempty, to make sure I get what I want, and it's a pretty big database, so trial and error is something I'd really like to avoid at this point...
Any help or suggestions highly appreciated, thanks!
Also, I am not 100% sure I should be using the NONEMPTY() function as opposed to the NON EMPTY keyword, so feel free to give some advice on that as well.
Hi @Feilin,
As this is the forum supports for Power BI and DAX, for more professional advice to MDX, please post question on SSAS forum.
Thanks,
Yuliana Gu
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
96 | |
89 | |
73 | |
61 | |
58 |