Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Using a measure for column in matrix

Hello,

 

I'm using these tables:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

D_PERIODS

 

 

 

 

 

 

 

D_USERTYPES

 

 

 

 

 

 

D_ACTIONS

D_DATES

 

Calculated columns in D_DATES

 

WEEK =
VAR _date_ = D_DATES[DATE]
VAR _weekday_ = WEEKDAY(_date_)
VAR _diff_ = IF(_weekday_ = 1; 7; 0)
VAR _monday_ = _date_ - _weekday_ - _diff_ + 2
VAR _sunday_ =_monday_ + 6
RETURN
"mo " & FORMAT(_monday_; "DD/MM/YYYY") & " - su " & FORMAT(_sunday_; "DD/MM/YYYY")   sorted by:
WEEK ORDER =
VAR _date_ = D_DATES[DATE]
VAR _weekday_ = WEEKDAY(_date_)
VAR _diff_ = IF(_weekday_ = 1; 7; 0)
VAR _monday_ = _date_ - _weekday_ - _diff_ + 2
RETURN
YEAR(_monday_) * 10000 + MONTH(_monday_) * 100 + DAY(_monday_)

 

MONTH = FORMAT(D_DATES[DATE];"MMM YYYY")           sorted by:
MONTH ORDER = YEAR(D_DATES[DATE]) * 100 + MONTH(D_DATES[DATE])
 
QUARTER = "Q" & FORMAT(D_DATES[DATE];"Q YYYY")    sorted by:
QUARTER ORDER = YEAR(D_DATES[DATE]) * 10 + QUARTER(D_DATES[DATE])
  
YEAR = YEAR(D_DATES[DATE])
 
 
I also have some Measures:
 
Selected Period Title =
SWITCH(SELECTEDVALUE(D_PERIODS[PERIOD]);
"Day"; "Amount per Day";
"Week"; "Amount per Week";
"Month"; "Amount per Month";
"Quarter"; "Amount per Quarter";
"Year"; "Amount per Year";
BLANK()
)
 
Selected Period Value =
SWITCH(SELECTEDVALUE(D_PERIODS[PERIOD]);
"Day"; FIRSTNONBLANK(D_DATES[DATE]; 1);
"Week"; FIRSTNONBLANK(D_DATES[WEEK]; 1);
"Month"; FIRSTNONBLANK(D_DATES[MONTH]; 1);
"Quarter"; FIRSTNONBLANK(D_DATES[QUARTER]; 1);
"Year"; FIRSTNONBLANK(D_DATES[YEAR]; 1);
BLANK()
)
 
F_ACTION_LOG
 
 
 
 
 
 
 
 
 
 
 
 
 
I want to use a Matrix with following settings
Rows
     'D_ACTIONS'[ACTION]
     'D_USERTYPES'[USERTYPES]
Columns
     'MyMeasures'[Selected Period Value]
Values
     Count of 'F_ACTION_LOG'[PK_ACTION_LOG]
 
I also want to filter this Matrix with a Slicer on value 'D_PERIODS'[PERIOD]
Depending on the period I choose the columns in the Matrix shoeld change
 
I should get this:
 
 
But it doesn't work:
Actualy I can't use the measure 'MyMeasures'[Selected Period Value] as column.
Why is this?
Can I add an other Measure?
 
I DO NOT want to use hierarchy (DATE - WEEK - MONTH - QUARTER - YEAR)
I DO NOT want to use 5 matrices an hide 4 matrices and show one matrix with bookmarks depending on 5 buttons and set the slicer
 
Is there a sollution for this using a Measure?
 
Thanks
 
R.W.
 
 
 
Need Help

3 Replies