Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |