CODEWITHSUNDEEP
daxmdx
Jot Sat
Jot Sat

Reputation: 1

Converting simple DAX expression to MDX

I need to convert a DAX expression I'm using i PowerBI to MDX to use in Excel > Olap > Calculated Measure (or member?). I'm no developer and do not have full access to the database, but connect to it using analysis services.

This DAX measure is working. I want to the same logic in Excel as a calculated measure (or calculated member?). Running measure displays sales quantity where the products has a 1 in either FilterA, FilterB or FilterC.

CALCULATE(
    [SalesQtn], 
    FILTER(
        'Product', 
        'Product'[FilterA] = "1" ||  'Product'[FilterB] = "1" ||  'Product'[FilterC] = "1"
        )
    )

I'm 98% newbie in In DAX and 100% newbie in MDX. I've managed to do like below in Calculated Measure to filter using FilterA = "1", but I recon a function is needed for combination FilterB and C using OR operation

([Measures.[SalesQtn],
[Product].[FilterA].&[1])

Upvotes: 0

Views: 641

Answers (1)

GregGalloway
GregGalloway

Reputation: 11625

Try the following:

SUM(
 {
 ([Product].[FilterA].&[1], [Product].[FilterB].[All], [Product].[FilterC].[All]),
 ([Product].[FilterA].[All], [Product].[FilterB].&[1], [Product].[FilterC].[All]),
 ([Product].[FilterA].[All], [Product].[FilterB].[All], [Product].[FilterC].&[1])
 },
 [Measures].[SalesQtn]
)

Or if there are some rows where two FilterA and FilterB are 1 that might produce a wrong result. In that case try:

Sum(
 Filter(
  [Product].[FilterA].[FilterA].Members
  * [Product].[FilterB].[FilterB].Members
  * [Product].[FilterC].[FilterC].Members,
  [Product].[FilterA].CurrentMember is [Product].[FilterA].&[1]
  Or [Product].[FilterB].CurrentMember is [Product].[FilterB].&[2]
  Or [Product].[FilterC].CurrentMember is [Product].[FilterC].&[1]
 ),

 [Measures].[SalesQtn]
)

Upvotes: 1

Related Questions