cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

Using DAX to filter

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?

Anonymous
Not applicable
``````// 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(
// 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.``````

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors