Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Need help to create dynamic matrix table filter with slicer date and parameter

Good day, 

I would like to create a dynmic matrix table based on slicer date and parameter of week#

Here is the matrix I created currently with hard code:

-----

>> Matrix content:

Matrix column: Slicer_date, Slicer_date+1week, Slicer_date+2week, ..., Slicer_date+10week
Matrix row: team

Matrix value: %test_done

-----

>> Items created
1. slicer: slicer_date[Date] 

2. 10 measures:

%test_done_Slicer_date, 

%test_done_Slicer_date+1week, 

%test_done_Slicer_date+2week, 

...

%test_done_Slicer_date+10week

 

>> the measure major body is : 

 

%test_done_Slicer_date =
var _maxDate = MAX(Slicer_date[Date])
var PassedCount =
CALCULATE(
COUNTROWS(tbl_test_case),
FILTER( 
ALLSELECTED(tbl_test_case[testcaseResult_execEndDate]),
(tbl_test_case[testcaseResult_execEndDate] <= _maxDate)
)
)

var PlannedCount =
CALCULATE(
COUNTROWS(tbl_test_case),
FILTER(
ALLSELECTED(tbl_test_case[testcaseRun_plannedEndDate]),
(tbl_test_case[testcaseRun_plannedEndDate] <= _maxDate)
)
)
return
if(PlannedCount=0 || PassedCount=0 || PlannedCount<>0 && PassedCount<>0 && PlannedCount - PassedCount < 0,
0,
IF(PlannedCount<>0 && PassedCount<>0 && PlannedCount = PassedCount,
100,
PassedCount/PlannedCount * 100)
)

 

>>for +1week, 2week, ..., 10week, I manually change to _maxDate+7, _maxDate+14,..., _maxDate+70

-----

 

After adding 10 measures into Matrix, the data change based on the selection of Slicer_date. 

 

Right now, I got new requirement that client would like to enter the number weeks from 1-20 and Matrix will show column: Slicer_date, Slicer_date+1week, Slicer_date+2week, ..., Slicer_date+20week dynamically.

 

What I can think of is to add parameter: week  #, but not sure how to use it to control the matrix since slicer_date only works on measure and they are manually created. Can I add this parameter into my current version, or I need to re-write my current matrix from scratch?

 

Any suggestion is welcomed.

 

Thanks in advance.

 

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    Anonymous , If want to select a date or a week, and want to display more, you need to have a slicer on disconnected date/week table. You will have joined date, Disconnected date and disconnected parameter table

     

    //Date1 is independent Date table, Date is joined with Table
    new measure =
    var _min= minx(allselected(Date1),Date1[Week Rank])
    var _max = _min + [Parameter Meausre]
    return
    calculate( sum(Table[Value]), filter('Date', 'Date'[Week Rank] >=_min && 'Date'[Week Rank] <=_max))

     

     

    Where week Rank can be created on week start date or YYYYMM as a new column

     

    Have these new columns in Date Table, Week Rank is Important in Date/Week Table

    Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

     

     

    Need of an Independent/disconnected Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

    Power BI How to use/compare two Date/Period slicers: https://youtu.be/WSeZr_-MiTg