Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
svishwanathan
Helper III
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
v-jiascu-msft
Employee
Employee

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.

View solution in original post

1 REPLY 1
v-jiascu-msft
Employee
Employee

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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