Using DAX to filter

PBCIT — Fri, 24 Jul 2020 15:43:20 GMT

Ok so I feel like I am missing something. I have a table that I use to get 3 or four other tables on the same axis in visuals (column charts, etc.). The table only has 2 columns and looks like this:

Depending on what "track" a person is on, we calculate stats for a different number of weeks. For instance, if a person is on track 1, they will have 9 weeks that we need to track. If a person is on track 2, they will have 8 weeks we need to track. If a person is on track 3, they will have 5 weeks we need to track. I have what I call a Program table that defines what the program is and how many weeks that program needs to be tracked for:

and then the program code is placed in a column for the particular individual in a different table that has information on the individual. My question is. How can I get a column chart to show only the number of weeks that the person is being tracked for? For instance, how can I get it to where, if the person is on Track 2, it will only show 8 in the axis? I have tried to use a couple of different DAX formula measures, but nothing has really panned out. Any ideas?

Re: Using DAX to filter

Anonymous — Fri, 24 Jul 2020 17:19:51 GMT

// Assuming that Weeks table is disconnected. // Create an auxiliary measure // that will return for each Week # // 1 if it should be shown and // 0/BLANK if it shouldn't. Then, filter // the visual by this measure's value // = 1. [Show Week #] = var __maxNumOfWeeksVisible = MAX( Program[# Weeks] ) var __result = SELECTEDVALUE( // this must start with 1 // and go up continuously // up to the number of weeks // in the table Weeks[ID], // this in case many weeks visible 1000 ) <= __maxNumOfWeeksVisible return 1 * __result // Bear in mind that if many people are // selected, then the number of weeks // visible will be the maximum number // of weeks among all the people and // their programs.

topic Using DAX to filter in DAX Commands and Tips

Using DAX to filter

Re: Using DAX to filter