none
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
    I had three slice .

    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

All replies