cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## 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
Employee

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.
Employee

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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors