Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

QUARTER problem

Dear community members,
Although from the first look issue seemed to be easy, I as a beginner still can not solve it.

I made calculated column Quarter = QUARTER([Date]), which perfectly returns quarters (1,2,3,4) both in data model and in slicer (4 options for quarters). I need different measures for each of quarter, i.e., IF (Quarter = 1, measure A, (IF Quarter = 2, measure B, (IF Quarter = 3, measure C, (IF Quarter = 4, measure D)))), so Quarter would be a reference to calculate 4 different measures.

But, because of row context (data model has many columns) Quarter function returns all instances of Quarters associated with [Date], which is shown in matrix table (such result also in Power BI) below.

[Date] dimension is quarterly based.

Some function/ group of functions are needed which tell that there are only 4 cases of Quarters.  I.e. something opposite ALL function, which not excludes, but includes row context of other columns of data model. I tried to manipulate QUARTER([Date]) with other functions, but QUARTER function seems not to like it.

Any ideas?:)
Regards,

 

Ervins

  • Fowmy's avatar
    Fowmy
    4 years ago

    Anonymous 

    When inserting the quarter column, it sums it. Instead, you can create your measure as follows:

    Measure = 

SWITCH(
    MAX( Table1[Quarter] ),
    1, [Measure A ],
    2, [Measure B ],
    3, [Measure C ],
    4, [Measure D ]
)


    Or, without the help of the Quarter column

    Measure = 

SWITCH(
     QUARTER( MAX (Table1[Date] )),
    1, [Measure A ],
    2, [Measure B ],
    3, [Measure C ],
    4, [Measure D ]
)

1 Reply

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

    Anonymous 

    When inserting the quarter column, it sums it. Instead, you can create your measure as follows:

    Measure = 

SWITCH(
    MAX( Table1[Quarter] ),
    1, [Measure A ],
    2, [Measure B ],
    3, [Measure C ],
    4, [Measure D ]
)


    Or, without the help of the Quarter column

    Measure = 

SWITCH(
     QUARTER( MAX (Table1[Date] )),
    1, [Measure A ],
    2, [Measure B ],
    3, [Measure C ],
    4, [Measure D ]
)