# dimension filter dax problem.

### General discussion

•  I try to do the model something problem.
My problem and example is

Sales fact table <-dimension times
Sales fact table <-dimension company
Sales fact table <-employee
Dimension table had date time and company.
Dim date
Company_id,datekey , working days,monthlyid
1, 2013/1/1, 20,201301
1,2013/2//1,19 ,201302
2,2013/1/1, 21 ,201301
3, 2013/1/1, 22    ,201301
.
.
.

The table  have all date between 2012/1/1 to 2013/12/31 by all company and all dimension table have company_id.

This is my repot Sample

filter company id =1
Date , amount ,total amount , working days
2013/1/3 ,200 ,   550         ,20
2013/1/4 ,200 ,   550          ,20
2013/1/7, 100 ,   550          ,20
2013/1/8, 90  ,   550          ,20
2013/1/9, 10  ,   550          ,20
2013/1/10, 100,   550          ,20
2013/1/11, 50 ,   550           ,20
.
.
.
.
Show the All 2013/1 data

Year -->2012,2013
month --> 1 to 12
company --> 1 , 2, 3

My report problem is the total amount. If I filters to company , that total amount got wrong data.

For example:
company id =2
2013/1 total : 300
this company 2013/1/2 not holiday

Ther report total  will be show  only have the date data company_id total, not all company_id total.
Date , amount ,total amount , working days
2013/1/2, 100,  300         ,21
2013/1/3 ,200 ,   850         ,20
2013/1/4 ,200 ,   850         ,20

I  writed the DAX

[total amount]:=if(HASONEFILTER('Dim date'[monthlyid]),
CALCULATE(sumx('Dim date',[sale amount]),
filter(all('Dim date'),
filter(VALUES('Dim date'[COMPANY_id]),'Dim date'[COMPANY_ID]=
EARLIER('Dim date'[COMPANY_ID])&&'Dim date'[Datekey]=EARLIER('Dim date'[Datekey]))),
SUMMARIZE('Dim date','Dim date'[MONTHLYID],'Dim date'[COMPANY_ID])),[sales total])

I don't know how to fix it. I am very confused this problem. If you have any idea , please kindly help me .

Best regards.

James.

james

Saturday, April 20, 2013 1:46 AM