cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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
Community Support Team

Re: Dax help-using variables

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

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.
1 REPLY 1
Community Support Team
Community Support Team

Re: Dax help-using variables

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

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.