Matrix & Slicer Not Playing Together

[leebaldwin]

Hey there:

So, I have a report that I have created a matrix and slicer for along with multiple measures. This is not something new as I have created these before; however, my matrix does not seem to to want to remove all the columns except for the one chosen. As you will see, I have chosen a single bucket, but the matrix is still showing all columns. I also captured all of the calculations that are used as well. What could I possibly be missing here?

[Anonymous]

Hi @leebaldwin,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like even after selecting a single aging bucket in the slicer, the matrix continues to display all the aging bucket columns, rather than just the one selected. This behavior is expected because Power BI does not dynamically hide or show measure columns in a matrix based on slicer selections unless you use field parameters or a single dynamic measure driven by a disconnected table and a SWITCH logic. Since the user is using separate static measures, selecting a bucket in the slicer does not remove the other measures from the matrix. 

As @MasonMA has responded to your query, kindly go through his response and check if the issue can be resolved.

I would also take a moment to thank @MasonMA and @lbendlin, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[MasonMA · Accepted solution]

@leebaldwin 

Hi,

You may need to use a disconnected table and use this table for your slicer.

AgingBuckets = DATATABLE(
    "Bucket", STRING,
    {
        {"Current"},
        {"1-30 Days"},
        {"31-60 Days"},
        {"61-90 Days"},
        {"91-120 Days"},
        {"121-150 Days"},
        {"151-180 Days"},
        {"181-365 Days"},
        {">365 Days"}
    }
)

then try using SWITCH and SELECTEDVALUE to switch your measures

SelectedBucketValue = 
SWITCH(
    SELECTEDVALUE('Aging Bucket'[Bucket]),
    "Current", [Current],
    "1-30 Days", [1_30_Days],
    "31-60 Days", [31_60_Days],
    "61-90 Days", [61_90_Days],
    "91-120 Days", [91_120_Days],
    "121-150 Days", [121_150_Days],
    "151-180 Days", [151_180_Days],
    "181-365 Days", [181_365_Days],
    ">365 Days", [Over_365_Days]
)

[lbendlin]

Are you using Field Parameters for these measures?

[leebaldwin]

My measures all look like <measure_name> = calculate(sum(table[col]),table[calculation_name] = "<bucket>"