Using a measure for column in matrix

[Anonymous]

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.

[v-janeyg-msft]

Hi, @Anonymous 

Can you share some sample data and your desired result instead of a lot of pictures? So we can help you soon. 

Best Regards

Janey Guo

[amitchandak · Accepted solution]

@Anonymous , This can done using bookmarks.

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

Or you can try calcultion groups

https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/

or

Dynamically change chart axis in Power BI
https://www.youtube.com/watch?v=6jeSIRpjv0M
https://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/

[Anonymous]

@amitchandak 

Thanks,

I already used the buttons/bookmark strategy before. It works, but it's cumbersome.
I'd hoped for a much more dyamic solution, like I used in QlikView

Didn't find time to look into the other two options.
As one of your suggestions does work, I'll mark your answer as a solution.

R.W.