Forum Discussion

charonT's avatar
charonT
Helper I
1 year ago
Solved

[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 would like to show the breakdown, it will show all the columns.

This is the DAX I am working on, are there any suggestion to solve this issue? Thank you in advance.

 

 

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 &&
            MAX(YEAR[YEAR]) >= YearSelection - 7,
            1,
            0
        ),
        IF (
            MAX(YEAR[YEAR]) <= YearSelection - 3 &&
            MAX(YEAR[YEAR]) >= YearSelection - 6,
            1,
            0
        )
    ),
    IF (
        Selection = "B",
        IF (
            Mode = "Part-time",
            IF (
                MAX(YEAR[YEAR]) <= YearSelection - 8 &&
                MAX(YEAR[YEAR]) >= YearSelection - 11,
                1,
                0
            ),
            IF (
                MAX(YEAR[YEAR]) <= YearSelection - 6 &&
                MAX(YEAR[YEAR]) >= YearSelection - 9,
                1,
                0
            )
        ),
        IF (
            Selection = "C",
            IF (
                MAX(YEAR[YEAR]) <= YearSelection - 3 &&
                MAX(YEAR[YEAR]) >= YearSelection - 6,
                1,
                0
            )
        )
    )
)

 

 

 

  • charonT's avatar
    charonT
    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
            )
        )
    )
)

     

7 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    Please show the expected outcome based on the sample data you provided.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • charonT's avatar
    charonT
    Helper I
    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
            )
        )
    )
)