cancel
Showing results for
Did you mean:
Highlighted
Member

## Dax help-using variables

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Dax help-using variables

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team

## Re: Dax help-using variables

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.