if statement not working properly

[snandy2011]

Hi all,

Please consider below situation,

I have one date table and i have two companies datasets as two tables.

Lets Say, company A and company B

so, company A data look like

date                   session

1/03/2010           20

20/03/2010         50

01/04/2010          80

25/04/2010         100

10/05/2010           30 

15/05/2010           150

01/06/2010             500

20/06/2010             200

so, if do monthwise segmentation, it will be look like,

Month name               Session

March                            70

April                               180

May                                180

June                                  700

 Now, i got 2nd company's data after pivoting,

Month Name            Session

March                        5

April                          10

May                         400

June                         200

July                           300

August                      100

Month name in both cases, I am taking from Dat table. and Date table has one to one relationship with both tables.

 what i want that,  i want to show company's A March session with company's B May session. for example,

If i do any bar graph, so in march section, there will be two bar, one is for comany's A March bar and Company's B May Bar.then again on April section, there will be also two bar,company A's April bar and company B's june bar. That means, I want to compare company A's march data with company B's May data.

Problem faced : Since i am taking month name from date table and it has one to one relationship with two company's table,so for that it is showing both companies same month data. I have tried to write a measure for company B, which is below,

if(

(VALUES('Date Table'[MonthName])="March",calculate(total session,filter(

'Date Table','Date Table'[MonthName]="May") ),if(VALUES('Date Table'[MonthName])="April",calculate(total session,filter(

'Date Table','Date Table'[MonthName]="June") ),123))

But, it shows only 123, it does not evaluate the calculate section into the If statement.But, if i put hard-coded number like 400 or 200. Then it will show perfectly.But I dont want to hard-coded.

Any idea how to achieve this problem? plz help me to solve this problem

Anu suggesation is really appreciable.

Thanks,

snandy

[v-lili6-msft · Accepted solution]

HI, @snandy2011

You need to use "all" Functions in your formula:

IF (
    VALUES ( 'Date Table'[MonthName] ) = "March",
    CALCULATE (
        [total session],
        FILTER ( ALL ( 'Date Table' ), 'Date Table'[MonthName] = "May" )
    ),
    IF (
        VALUES ( 'Date Table'[MonthName] ) = "April",
        CALCULATE (
            [total session],
            FILTER ( ALL ( 'Date Table' ), 'Date Table'[MonthName] = "June" )
        )
    )
)

Best Regards,

Lin

[snandy2011]

Hi @v-lili6-msft ,

Thanks for your replying..After trying lot of, i achieved by Allselected. yes , with all function, it also works,

Thanks for your solution..