Fix value in the measure

[PeterChen]

I want to create a VAR in the measure that calculate the number by groups and year and quarter.

For example,

Date              Groups    Numbers

2015/1/1       A             1

2015/1/2       A             2

2015/2/1       A             5

2015/4/1       A             2

2015/4/2       A             10

2015/6/5       A             4

What I want in report is:

Year       Quarter      Groups       Numbers       NEW

2015       Qtr 1          A               8                     8

2015       Qtr 2          A               16                   8

The NEW is the total value of min([Date].[Year]) & min([Date].[Quarter]) by Groups.

NEW = 
VAR MINYR = MIN(dt[date].[Year])
VAR MINQR = MIN(dt[date].[QuarterNo])
VAR NEW= CALCULATE(SUM(dt[total]), FILTER(ALLEXCEPT(dt, dt[Groups]), dt[date].[Year] = MINYR && dt[date].[QuarterNo] = MINQR))
Return NEW

However, this return:

Year       Quarter      Groups       Numbers       NEW

2015       Qtr 1          A               8                     8

2015       Qtr 2          A               16                   16

[v-frfei-msft · Accepted solution]

Hi @PeterChen ,

To create a measure as below.

NEW = 
VAR MINYR = CALCULATE(MIN(dt[date].[Year]),ALLEXCEPT(dt,dt[Group]))
VAR MINQR = CALCULATE(MIN(dt[date].[QuarterNo]),ALLEXCEPT(dt,dt[Group]))
VAR NEW= CALCULATE(SUM(dt[Numbers]), FILTER(ALL(dt), dt[date].[Year] = MINYR && dt[date].[QuarterNo] = MINQR))
Return 
IF(MAX(dt[Numbers]) = BLANK(),BLANK(),NEW)

[PeterChen]

---

@v-frfei-msft wrote:

Hi @PeterChen ,

To create a measure as below.

NEW = 
VAR MINYR = CALCULATE(MIN(dt[date].[Year]),ALLEXCEPT(dt,dt[Group]))
VAR MINQR = CALCULATE(MIN(dt[date].[QuarterNo]),ALLEXCEPT(dt,dt[Group]))
VAR NEW= CALCULATE(SUM(dt[Numbers]), FILTER(ALL(dt), dt[date].[Year] = MINYR && dt[date].[QuarterNo] = MINQR))
Return 
IF(MAX(dt[Numbers]) = BLANK(),BLANK(),NEW)

---

Could you please explain the last line?

What does Blank() mean?

Is it possible to assign a VAR to that?

For example, WANT = IF(MAX(dt[Numbers]) = Blank(), Blank(), New)

[v-frfei-msft]

Hi @PeterChen ,

If we just remove the last line, the result would be shown as below, which doesn’t make sense coz additional blank number occurred in Q3&Q4:

Then we add the IF filter to fix it:

IF(MAX(dt[Numbers]) = BLANK(),BLANK(),NEW)

BTW, we can replace it using VAR:

WANT = 
VAR MINYR = CALCULATE(MIN(dt[date].[Year]),ALLEXCEPT(dt,dt[Group]))
VAR MINQR = CALCULATE(MIN(dt[date].[QuarterNo]),ALLEXCEPT(dt,dt[Group]))
VAR NEW= CALCULATE(SUM(dt[Numbers]), FILTER(ALL(dt), dt[date].[Year] = MINYR && dt[date].[QuarterNo] = MINQR))
VAR WANT = IF(MAX(dt[Numbers]) = Blank(), Blank(), [NEW])
Return
WANT

[PeterChen]

---

@v-frfei-msft wrote:

Hi @PeterChen ,

If we just remove the last line, the result would be shown as below, which doesn’t make sense coz additional blank number occurred in Q3&Q4:

Then we add the IF filter to fix it:

IF(MAX(dt[Numbers]) = BLANK(),BLANK(),NEW)

BTW, we can replace it using VAR:

WANT = 
VAR MINYR = CALCULATE(MIN(dt[date].[Year]),ALLEXCEPT(dt,dt[Group]))
VAR MINQR = CALCULATE(MIN(dt[date].[QuarterNo]),ALLEXCEPT(dt,dt[Group]))
VAR NEW= CALCULATE(SUM(dt[Numbers]), FILTER(ALL(dt), dt[date].[Year] = MINYR && dt[date].[QuarterNo] = MINQR))
VAR WANT = IF(MAX(dt[Numbers]) = Blank(), Blank(), [NEW])
Return
WANT

---

Awesome.

But want seems not work if I add another VAR:

REALWANT = 
VAR MINYR = CALCULATE(MIN(dt[date].[Year]),ALLEXCEPT(dt,dt[Group])) 
VAR MINQR = CALCULATE(MIN(dt[date].[QuarterNo]),ALLEXCEPT(dt,dt[Group])) 
VAR NEW= CALCULATE(SUM(dt[Numbers]), FILTER(ALL(dt), dt[date].[Year] = MINYR && dt[date].[QuarterNo] = MINQR)) 
VAR WANTDenominator = IF(MAX(dt[Numbers]) = Blank(), Blank(), [NEW]) 
VAR WANTNominator = CALCULATE(SUM(dt[Numbers]), ALLEXCEPT(dt,dt[Group],dt[date].[Year],dt[date].[QuarterNo])) 
Return
WANTNominator/WANTDenominator 

This return 1 in all other years and quarters, seems weird.

It should return 1 'only' in the first year quarte, said 2015 Q1 under each group.

Year       Quarter      Groups       Numbers       REALWANT

2015       Qtr 1          A               8                     1

2015       Qtr 2          A               16                   2

2015       Qtr 3          A               20                   2.5

2015       Qtr 4          A               16                   2

2016       Qtr 1          A               40                   5

2016       Qtr 2          A               32                   4

2016       Qtr 3          A               12                  1.5

2016       Qtr 4          A               8                    1

[v-frfei-msft]

Hi @PeterChen ,

I’ve completed the data with 4 Qtrs in 2015&2016. And the measure works well in my side:

[PeterChen]

---

@v-frfei-msft wrote:

Hi @PeterChen ,

I’ve completed the data with 4 Qtrs in 2015&2016. And the measure works well in my side:

---

Is it possible that there are other features in the data even I did not use it in this table so that the return value becomes 1 in all year and quarter?