Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3468
  • Last Modified:

MDX Query filter column with value greater than zero

I have this query

Select  
{[Buckets].[Header].[AGHeader]} on columns
,

Filter(
{[Account].[Accounts Name].[Name]} ,
([Measures].[Summary_Value]>0)
) ON ROWS


from capa
Where  [Buckets].[Type].&[AR]


which produces output similar to

-----------  Col1 col2 col3

ABC CO      1      0      1
BCE Co       2      0      2
HEO CO      3      1     2


I need to filter so results only contain the rows where col2 has a value greater than zero.

I am new to mdx so not sure what other info I need to provide.

0
JonMny
Asked:
JonMny
  • 3
  • 2
2 Solutions
 
derekkrommCommented:
Is AGHeader a named set? I tried basically an identical select on one of my dbs and it filtered as expected.

What if you try this:

Select  
{[Buckets].[Header].[AGHeader],[Measures].[Summary_Value]} on columns,

Filter(
{[Account].[Accounts Name].[Name]} ,
([Measures].[Summary_Value]>0)
) ON ROWS


from capa
Where  [Buckets].[Type].&[AR]
0
 
JonMnyAuthor Commented:
no not a named set,

The query seems to filter values that are zero but It's not quite what I need

I need to show all agheaders  and value for a specific account but filter to only the times when col2 has a zero value. the other columns can have a zero.




0
 
itcoupleCommented:
Hi

Might be worth posting entire query. The syntax you used is correct. Just ensure you use the correct measures that generates values for column 2.

Regards
Emil
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
JonMnyAuthor Commented:
I will try to create a more complete example.


here is a what i want to due but of course doesnt work.

from capa
Where  [Buckets].[Type].&[AR]
and
( [Buckets].[AgingHeader].&[52] and [Measures].[Invoice_Amount]>0)


0
 
itcoupleCommented:
Hi

Is that the complete query?

Using where may produce unexpected results with MDX. use [Measures].[Invoice_Amount]>0) using filter on rows.

If you know T-SQL (like me) then forget about it!! It will be much easier :)

Regards
Emil

0
 
JonMnyAuthor Commented:
The issue I am having is that all three columns are tied to the invoice_amount. So if I filter invoice amount> 0 I end up filtering columns that I need.


0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now