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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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