# an MDX question::

• ### Question

• We have a table as the following

product store day visit
p1     s1      d1  1
p2     s1      d1  0
p3     s1      d1  0

p1     s1      d2  0
p2     s1      d2  0
p3     s1      d2  0

Since if we visit a store, actually we will look at each product. So we'd like to flag the visit as 1 for ALL the products in a store that day if we visit that store that day for ANY product.

The expected result should be like

product store day visit
p1     s1      d1  1
p2     s1      d1  0   ==> flag visit to 1 as p1 was visited
p3     s1      d1  0   ==> flag visit to 1 as p1 was visited.

p1     s1      d2  0
p2     s1      d2  0
p3     s1      d2  0

I tried use the following 2 way, but neither works.

([PRODUCT].[PRODUCT].[PRODUCT].members*[Store].[Store].[Store].members*[Calendar].[Date].[Date].members
,[measures].[new visit]) = IIF(([Store].[Store].currentmember,[Calendar].[Date].currentmember,visit) > 0,1,0 );

([PRODUCT].[PRODUCT].[PRODUCT].members*[Store].[Store].[Store].members*[Calendar].[Date].[Date].members
,[measures].[new visit]) = IIF((root(product),[Store].[Store].currentmember,[Calendar].[Date].currentmember,visit) > 0,1,0 );

Friday, September 30, 2011 3:15 AM

• This is how you can do it.

You can use the MAX function and get to what you want.

In Adventure works what i have done is considered the Internet Sales value by Product Category and the Month. Now Set the Internet sales value across all month of the year per product category to its Max value.

In your case since values are only 0s and 1s, and if there is a visit any one record will be marked as 1, Max would return 1 and you use this to set across that store for the day. You can solve your problem too similarly.

```WITH MEMBER MAX_InternetSales As
GENERATE
(
ANCESTOR( [Date].[Calendar].CURRENTMEMBER ,[Date].[Calendar].[Calendar Year]),
MAX
(
(
[Product].[Product Categories].CURRENTMEMBER,
([Date].[Calendar Year].CURRENTMEMBER,[Date].[Calendar].[Month])
)
,
[Measures].[Internet Sales Amount]
)
),FORMAT_STRING='CURRENCY'

SELECT
{[Measures].[Internet Sales Amount],MAX_InternetSales} on 0,
NONEMPTY(
[Product].[Product Categories].[Category]*
[Date].[Calendar].[Month],
[Measures].[Internet Sales Amount]
) On 1
FROM
```

vinu
Friday, September 30, 2011 2:52 PM
• Try the following equivalent in Adventure Works

```WITH MEMBER [Measures].[Visit] AS
IIF((	[Customer].[Customer Geography].CurrentMember,
[Date].[Calendar].CurrentMember,
[Measures].[Internet Sales Amount]) > 0, 1, 0)

SELECT {[Measures].[Internet Sales Amount], [Measures].[Visit]} on 0,
[Customer].[Customer Geography].[City].&[Berkshire]&[ENG] *
[Date].[Calendar].[Calendar Year].Members *
[Product].[Product Categories].[Category].Members  ON 1
```

regards

Gautham

Friday, September 30, 2011 3:38 PM

### All replies

• I think it is easier if the flags are appropriately set in the ETL layer itself. You do not have to do anything at SSAS calculation.

Something like this should work

```Create table #tmp (Product varchar(2),Store varchar(2), VisitDay varchar(2), VisitFlag varchar(1))
Insert into #tmp values('p1','s1','d1','1')
Insert into #tmp values('p2','s1','d1','0')
Insert into #tmp values('p3','s1','d1','0')
Insert into #tmp values('p1','s1','d2','0')
Insert into #tmp values('p2','s1','d2','0')
Insert into #tmp values('p3','s1','d2','0')

With StoreMaxVisitFlag as (
Select MAX(VisitFlag) VisitFlag,VisitDay,Store from #tmp group by VisitDay,Store)

Select * from #tmp t1 inner join StoreMaxVisitFlag s1 on t1.Store=s1.Store and t1.VisitDay=s1.VisitDay

With StoreMaxVisitFlag as (
Select MAX(VisitFlag) VisitFlag,VisitDay,Store from #tmp group by VisitDay,Store)

Update t1 set t1.VisitFlag=s1.VisitFlag from #tmp t1
inner join StoreMaxVisitFlag s1 on t1.Store=s1.Store and t1.VisitDay=s1.VisitDay```

vinu
Friday, September 30, 2011 4:34 AM
• Thanks Vinu,

I also though about that way, and it should be performance much better than MDX. But just curious, how to do it in MDX?

Friday, September 30, 2011 4:54 AM
• This is how you can do it.

You can use the MAX function and get to what you want.

In Adventure works what i have done is considered the Internet Sales value by Product Category and the Month. Now Set the Internet sales value across all month of the year per product category to its Max value.

In your case since values are only 0s and 1s, and if there is a visit any one record will be marked as 1, Max would return 1 and you use this to set across that store for the day. You can solve your problem too similarly.

```WITH MEMBER MAX_InternetSales As
GENERATE
(
ANCESTOR( [Date].[Calendar].CURRENTMEMBER ,[Date].[Calendar].[Calendar Year]),
MAX
(
(
[Product].[Product Categories].CURRENTMEMBER,
([Date].[Calendar Year].CURRENTMEMBER,[Date].[Calendar].[Month])
)
,
[Measures].[Internet Sales Amount]
)
),FORMAT_STRING='CURRENCY'

SELECT
{[Measures].[Internet Sales Amount],MAX_InternetSales} on 0,
NONEMPTY(
[Product].[Product Categories].[Category]*
[Date].[Calendar].[Month],
[Measures].[Internet Sales Amount]
) On 1
FROM
```

vinu
Friday, September 30, 2011 2:52 PM
• Try the following equivalent in Adventure Works

```WITH MEMBER [Measures].[Visit] AS
IIF((	[Customer].[Customer Geography].CurrentMember,
[Date].[Calendar].CurrentMember,
[Measures].[Internet Sales Amount]) > 0, 1, 0)

SELECT {[Measures].[Internet Sales Amount], [Measures].[Visit]} on 0,
[Customer].[Customer Geography].[City].&[Berkshire]&[ENG] *
[Date].[Calendar].[Calendar Year].Members *
[Product].[Product Categories].[Category].Members  ON 1
```

regards

Gautham

Friday, September 30, 2011 3:38 PM