Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |