Dax help-using variables

[svishwanathan]

Hi

I want to create a measure that adds the sales in a given period. There is a column in the sales/actuals table called #Day which is a lookup from my calendar table. This column contains a unique index number for each date.  I have another table called Segment where I have created the following dimensions

Segment                       Max                            Min

 A                                     1                                30

B                                        1                               60

C                                        1                               90

So when user selects segment A, I want sales between days 1 thru 30, for segment B I want sales between days 1 thru 60 and so on.

As I mentioned, the # day column in the Acutals table contains these max and min values.

I wrote this formula:

ActualsSegment = VAR lowerlimit=if(hasonevalue('Segment'[Segment]),VALUES('Segment'[Min]),BLANK()) VAR higherlimit=if(hasonevalue('Segment'[Segment]),VALUES('Segment'[Max]),BLANK()) RETURN CALCULATE([Actuals],
FILTER(Actuals,
Actuals[#Day]>=lowerlimit && Actuals[#Day]<=higherlimit)
)

What is happening is that in the Actuals table, only rows with the max and min dates are getting filtered whereas I want all rows that contain #days between lowerlimit and higher limit are included

To elaborate, if I select Segment A, the measure returnes only actuals on rows containing # Day as 1 and 30 instead of all rows that contain any # day between 1 and 30

[v-jiascu-msft · Accepted solution]

Hi @svishwanathan,

The Segment table should be independent of other tables, which means no relationship can be established. And also try to wrap Actuals with ALL like below.

ActualsSegment =
VAR lowerlimit =
    IF ( HASONEVALUE ( 'Segment'[Segment] ), VALUES ( 'Segment'[Min] ), BLANK () )
VAR higherlimit =
    IF ( HASONEVALUE ( 'Segment'[Segment] ), VALUES ( 'Segment'[Max] ), BLANK () )
RETURN
    CALCULATE (
        [Actuals],
        FILTER (
            ALL ( Actuals[#Day] ),
            Actuals[#Day] >= lowerlimit
                && Actuals[#Day] <= higherlimit
        )
    )

Best Regards,

Dale