Forum Discussion
Why use a return inside a sumx
- 2 years ago
GuillaumePower I agree that the construction of the measure is odd and complex. However, without additional information regarding the model, the intended use case, etc. it's hard to know why it was constructed the way it was. It may be that there is a good reason for it or it may be that it could be done much more simply and efficiently.
You can use nested VAR statements in a measure, for example, you could write this:
Sales PQ = VAR __Result = IF ( [ShowValueForDates], SUMX ( VALUES ( 'Date'[Fiscal Year Quarter Number] ), VAR CurrentFiscalYearQuarterNumber = 'Date'[Fiscal Year Quarter Number] VAR DaysSelected = CALCULATETABLE ( VALUES ( 'Date'[Day of Fiscal Quarter Number] ), REMOVEFILTERS ( 'Date'[Working Day], 'Date'[Day of Week], 'Date'[Day of Week Number] ), 'Date'[DateWithSales] = TRUE ) RETURN CALCULATE ( [Sales Amount], 'Date'[Fiscal Year Quarter Number] = CurrentFiscalYearQuarterNumber - 1, DaysSelected, ALLEXCEPT ( 'Date', 'Date'[Working Day], 'Date'[Day of Week] ) ) ) ) RETURN __ResultThere are simpler ways to get a previous quarter calculation, such as the following examples:
Internet Sales (PQ) = CALCULATE([Internet Sales], FILTER(ALL('Dates'), 'Dates'[Date] >= MIN('Dates'[Prior Quarter Date]) && 'Dates'[Date] <= MAX('Dates'[Prior Quarter Date]))) TI PQ 2 = CALCULATE( [Internet Sales], DATESBETWEEN('Dates'[Date], MIN('Dates'[Prior Quarter Date]), MAX('Dates'[Prior Quarter Date])) ) NC Intenet Sales (PQ) = VAR __MinPYDate = MIN('Dates'[Prior Quarter Date]) VAR __MaxPYDate = MAX('Dates'[Prior Quarter Date]) VAR __PQCalendar = CALENDAR(__MinPYDate, __MaxPYDate) VAR __Table = SUMMARIZE( ALL('FactInternetSales'), 'FactInternetSales'[OrderDate], "__Sales", SUM('FactInternetSales'[SalesAmount])) VAR __Result = SUMX( FILTER( __Table, [OrderDate] IN __PQCalendar), [__Sales] ) RETURN __Result
Why should not more use in this measure a
MyMeasure =
Var TheSum= Sumx(...)
Return TheSum
How is it possible in one single measure to send many times a value : one for each return, isn't' it ?
More, we have to think too, to the context of the measure, it seems very tricky for me.
GuillaumePower I agree that the construction of the measure is odd and complex. However, without additional information regarding the model, the intended use case, etc. it's hard to know why it was constructed the way it was. It may be that there is a good reason for it or it may be that it could be done much more simply and efficiently.
You can use nested VAR statements in a measure, for example, you could write this:
Sales PQ =
VAR __Result =
IF (
[ShowValueForDates],
SUMX (
VALUES ( 'Date'[Fiscal Year Quarter Number] ),
VAR CurrentFiscalYearQuarterNumber = 'Date'[Fiscal Year Quarter Number]
VAR DaysSelected =
CALCULATETABLE (
VALUES ( 'Date'[Day of Fiscal Quarter Number] ),
REMOVEFILTERS (
'Date'[Working Day],
'Date'[Day of Week],
'Date'[Day of Week Number]
),
'Date'[DateWithSales] = TRUE
)
RETURN
CALCULATE (
[Sales Amount],
'Date'[Fiscal Year Quarter Number] = CurrentFiscalYearQuarterNumber - 1,
DaysSelected,
ALLEXCEPT ( 'Date', 'Date'[Working Day], 'Date'[Day of Week] )
)
)
)
RETURN
__Result
There are simpler ways to get a previous quarter calculation, such as the following examples:
Internet Sales (PQ) =
CALCULATE([Internet Sales],
FILTER(ALL('Dates'),
'Dates'[Date] >= MIN('Dates'[Prior Quarter Date]) &&
'Dates'[Date] <= MAX('Dates'[Prior Quarter Date])))
TI PQ 2 =
CALCULATE(
[Internet Sales],
DATESBETWEEN('Dates'[Date], MIN('Dates'[Prior Quarter Date]), MAX('Dates'[Prior Quarter Date]))
)
NC Intenet Sales (PQ) =
VAR __MinPYDate = MIN('Dates'[Prior Quarter Date])
VAR __MaxPYDate = MAX('Dates'[Prior Quarter Date])
VAR __PQCalendar = CALENDAR(__MinPYDate, __MaxPYDate)
VAR __Table =
SUMMARIZE(
ALL('FactInternetSales'),
'FactInternetSales'[OrderDate],
"__Sales", SUM('FactInternetSales'[SalesAmount]))
VAR __Result = SUMX( FILTER( __Table, [OrderDate] IN __PQCalendar), [__Sales] )
RETURN
__Result