1

I'm having a hard time to understand how to filter out result of multidimensional expression.

This is my database schema.

A simple database schema

And this is my data.

Manufacturer
╔════╦═══════════════════╗
║ Id ║       Name        ║
╠════╬═══════════════════╣
║  1 ║ Awesome Computers ║
║  2 ║ TailSpin Toys     ║
╚════╩═══════════════════╝

Item
╔════╦═════════╦════════════════╦═══════╗
║ Id ║  Name   ║ ManufacturerId ║ Stock ║
╠════╬═════════╬════════════════╬═══════╣
║  1 ║ PC      ║              1 ║    40 ║
║  2 ║ Server  ║              1 ║    10 ║
║  3 ║ STB     ║              2 ║    80 ║
║  4 ║ Console ║              2 ║    50 ║
╚════╩═════════╩════════════════╩═══════╝

Part
╔════╦══════════════════╦════════╦══════════╦═══════╗
║ Id ║       Name       ║ ItemId ║ StatusId ║ Stock ║
╠════╬══════════════════╬════════╬══════════╬═══════╣
║  1 ║ MBO              ║      1 ║        1 ║   100 ║
║  2 ║ Processor        ║      1 ║        1 ║   100 ║
║  3 ║ Server MBO       ║      2 ║        2 ║    20 ║
║  4 ║ Server processor ║      2 ║        2 ║    20 ║
║  5 ║ Main box         ║      3 ║        2 ║    40 ║
║  7 ║ Adapter          ║      3 ║        3 ║    30 ║
║  8 ║ Controller       ║      4 ║        2 ║    40 ║
║ 10 ║ Adapter          ║      4 ║        1 ║    60 ║
║ 11 ║ Memory card      ║      4 ║        2 ║    80 ║
╚════╩══════════════════╩════════╩══════════╩═══════╝

Status
╔════╦═════════════╗
║ Id ║    Name     ║
╠════╬═════════════╣
║  1 ║ No data     ║
║  2 ║ Available   ║
║  3 ║ Unavailable ║
╚════╩═════════════╝

I imported everything into the tabular model solution. After this, I created two measures:

  • Table Item: ItemStock:=SUM([Stock])
  • Table Part: PartStock:=SUM([Stock])

Then I deployed the cube to the server.

By running the following MDX query...

SELECT 
    NON EMPTY { 
        [Part].[Name].CHILDREN
    } ON ROWS,
    { 
        [Measures].[PartStock]
    } ON COLUMNS
FROM [Model]
WHERE (
    {
        [Status].[Id].&[1]
    }
)

...I get this resultset...

╔═══════════╦═══════════╗
║           ║ PartStock ║
╠═══════════╬═══════════╣
║ Adapter   ║        60 ║
║ MBO       ║       100 ║
║ Processor ║       100 ║
╚═══════════╩═══════════╝

...which is ok.

However, when running this MDX query...

SELECT 
    NON EMPTY { 
        [Item].[Name].CHILDREN
    } ON ROWS,
    { 
        [Measures].[ItemStock]
    } ON COLUMNS
FROM [Model]
WHERE (
    {
        [Status].[Id].&[1]
    }
)

...I'm getting this resultset...

╔═════════╦═══════════╗
║         ║ ItemStock ║
╠═════════╬═══════════╣
║ Console ║        50 ║
║ PC      ║        40 ║
║ Server  ║        10 ║
║ STB     ║        80 ║
╚═════════╩═══════════╝

I was expecting that items in the ItemStock table would be filtered out by Part table as a many-to-many relationship. E.g. MBO, Processor and Adapter have references to items 1 and 4, so the result would be constrained to them, and the result should turn out like this:

╔═════════╦═══════════╗
║         ║ ItemStock ║
╠═════════╬═══════════╣
║ Console ║        50 ║
║ PC      ║        40 ║
╚═════════╩═══════════╝

What am I doing wrong?

3

Assuming we are talking about tabular models in SSAS 2012 or 2014 (since 2016 has a new capability around bi-directional crossfiltering) crossfiltering only flows over relationships in one direction. So filtering Status will crossfilter Part, but not Item or Manufacturer. Filtering Manufacturer will crossfilter Item and Part but not Status.

Any calculation you embed in a Tabular model must be written in DAX. So if you want to filter on Status and have it impact the ItemStock measure you need to code that:

ItemStock :=
IF(
  ISCROSSFILTERED('Status'[Id]),
  CALCULATE(
    SUM( 'Item'[Stock] ),
    SUMMARIZE(
        'Part',
        'Item'[Id]
    )
  ),
  SUM( 'Item'[Stock] )
)

More theory on many-to-many formulas in DAX:

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.