We use Excel from Office 365 with a SQL Server 2019 Tabular model
I have a "strange" behaviour of Excel Pivot regarding the level at which calculations are requested from DAX.
I create 3 simple (SQL Server) tables, MyDim, DimColour and MyFact
There is an implicit hierachy (not enforced) between the BrandName and ItemName in the MyDim table.
I could not enforce this because nothing says that an Item can only belong to one brand.
I create a very basic measure to aggregate the fact although I don't really need it here
FactTotal := SUM(MyFact[FactValue])
And I also create 2 additional measures that are more interesting
LowestLevel :=
SWITCH (
TRUE (),
ISFILTERED ( 'MyDim'[ItemName] ), "Item",
ISFILTERED ( 'MyDim'[BrandName] ), "Brand",
"Neither Brand nor Item"
)
Lowest Level ALWAYS return a value, regardless of whether there are rows in the fact table.
LowestLevelButItem :=
SWITCH (
TRUE (),
ISFILTERED ( 'MyDim'[ItemName] ), BLANK (),
ISFILTERED ( 'MyDim'[BrandName] ), "Brand",
"Neither Brand nor Item"
)
LowestLevelButItem returns a value as long as we are not filtering on ItemName
Now, this is the behaviour which bothers us...
Open the cube in an Excel Pivot table, filter on colours Blue and Yellow
Result 1, ok
This is the right result. No problem here.
Add the measure LowestLevelButItem...
Result 2, unexpected
Now, this is not the result we would like... I would not expect to see "Brand A" at all in this result
It shows that the measure displayed in D4 has been evaluated at a different level than D5 & D6.
D5 & D6 are evaluated at the Item level, as expected while D4 is evaluated at a higher level because there is no matching row at Item level...
Now, add the measure LowestLevel
Result 3, ok
Now, I understand this result.
Because LowestLevel ALWAYS returns a value, it makes Brand A / Item 1 visible, which forces evaluation of the measure LowestLevelButItem at the Item level, hence blank().
The problem I have is with the second result...
Question
How could I force the Pivot table to NOT return the Brand if there is no suitable Item underneath?
SSMS Behaviour
If I browse my cube in SSMS, I get the results I would expect, which is that my measures are always evaluated at the leaf level only and rows are not returned unless they have leaf level members.
SSMS Result 1, same
SSMS Result 2
SSMS did not return a row for "Brand A" with no Item member
SSMS Result 3
SQL Script to reproduce
CREATE TABLE MyDim(MyDimId INT NOT NULL PRIMARY KEY
,BrandName VARCHAR(100) NOT NULL
,ItemName VARCHAR(100) NOT NULL
);
GO
-- TRUNCATE TABLE MyDim;
INSERT INTO MyDim(MyDimId, BrandName, ItemName)
VALUES(1, 'Brand A', 'Item 1')
,(2, 'Brand B', 'Item 2')
,(3, 'Brand B', 'Item 3');
GO
CREATE TABLE DimColour(Colour VARCHAR(100) NOT NULL PRIMARY KEY);
GO
INSERT INTO DimColour(Colour)
VALUES('Red')
, ('Blue')
, ('Yellow');
CREATE TABLE MyFact(MyDimId INT NOT NULL
,Colour VARCHAR(100) NOT NULL
,FactValue INT NOT NULL
,PRIMARY KEY(MyDimId, Colour)
);
GO
-- TRUNCATE TABLE MyFact;
INSERT INTO MyFact(MyDimId, Colour, FactValue)
VALUES(1, 'Red', 1)
, (2, 'Blue', 2)
, (3, 'Blue', 4)
, (3, 'Yellow', 8);
MDX generated for Result 2 queries
- By SSMS (producing the result I want)
- Excel MDX
Just add the following IF clause to your measure: