Forum Discussion
charonT
1 year agoHelper I
[Question] Filtering columns in a matrix visual with DAX
I am trying to use a DAX measure to filter the columns in a matrix visualization based on different conditions. The DAX works well if I don't add any fields in the "rows". However, when I wou...
- 1 year ago
Thank you! I managed to sort this out on my own. The issue was that one of the DAX commands should have been set to MIN instead of MAX—a silly mistake on my part, haha. Interestingly, I’m not sure why it worked when there were no fields in the rows. The corrected DAX formula is provided below:
year filter = VAR Selection = SELECTEDVALUE('Metrics'[Metrics]) VAR YearSelection = SELECTEDVALUE('Data release'[Year of data release]) VAR Mode = SELECTEDVALUE('STARTMODE'[Mode]) RETURN IF ( Selection = "A", IF ( Mode = "Part-time", IF ( MAX(YEAR[YEAR]) <= YearSelection - 4 && MIN(YEAR[YEAR]) >= YearSelection - 7, 1, 0 ), IF ( MAX(YEAR[YEAR]) <= YearSelection - 3 && MIN(YEAR[YEAR]) >= YearSelection - 6, 1, 0 ) ), IF ( Selection = "B", IF ( Mode = "Part-time", IF ( MAX(YEAR[YEAR]) <= YearSelection - 8 && MIN(YEAR[YEAR]) >= YearSelection - 11, 1, 0 ), IF ( MAX(YEAR[YEAR]) <= YearSelection - 6 && MIN(YEAR[YEAR]) >= YearSelection - 9, 1, 0 ) ), IF ( Selection = "C", IF ( MAX(YEAR[YEAR]) <= YearSelection - 3 && MIN(YEAR[YEAR]) >= YearSelection - 6, 1, 0 ) ) ) )
charonT
1 year agoHelper I
Thank you! I managed to sort this out on my own. The issue was that one of the DAX commands should have been set to MIN instead of MAX—a silly mistake on my part, haha. Interestingly, I’m not sure why it worked when there were no fields in the rows. The corrected DAX formula is provided below:
year filter =
VAR Selection = SELECTEDVALUE('Metrics'[Metrics])
VAR YearSelection = SELECTEDVALUE('Data release'[Year of data release])
VAR Mode = SELECTEDVALUE('STARTMODE'[Mode])
RETURN
IF (
Selection = "A",
IF (
Mode = "Part-time",
IF (
MAX(YEAR[YEAR]) <= YearSelection - 4 &&
MIN(YEAR[YEAR]) >= YearSelection - 7,
1,
0
),
IF (
MAX(YEAR[YEAR]) <= YearSelection - 3 &&
MIN(YEAR[YEAR]) >= YearSelection - 6,
1,
0
)
),
IF (
Selection = "B",
IF (
Mode = "Part-time",
IF (
MAX(YEAR[YEAR]) <= YearSelection - 8 &&
MIN(YEAR[YEAR]) >= YearSelection - 11,
1,
0
),
IF (
MAX(YEAR[YEAR]) <= YearSelection - 6 &&
MIN(YEAR[YEAR]) >= YearSelection - 9,
1,
0
)
),
IF (
Selection = "C",
IF (
MAX(YEAR[YEAR]) <= YearSelection - 3 &&
MIN(YEAR[YEAR]) >= YearSelection - 6,
1,
0
)
)
)
)