I have been working to revamp a report I put together to reduce the number of pages to handle multiple department. I created a page “Duplicate of Service Dept Surveys”.
On the duplicate page WinsbyStatus2021.pbix (599.9 KB) I have been trying to figure out how to conditionally grab a column from different tables depending on a selected value. I am almost there, but the way I am writing my dax is not exactly working the way I need. In the visual if you set the Axis to by the table/column related to the department “Service” it works as expected. My thought was that I could create a measure that would conditionally select a table. For instance if the department was = “Parts” the Dax code would pull from the PartsFollowupTable. I would then put that measure in the Axis. Not working! I am wondering if I should be revisiting my data model to resolve the issue. Any guidance would be appreciated.
Below is a screenshot of the visual that when selected value is Service it works as expected. When I select Parts you see it does not work as expected. Second screenshot. This is due to the Axis being set to a specific table, ServiceFollowupTable. That is why I thought creating a measure for the Axis would work.
Your instincts were exactly right when you said " I am wondering if I should be revisiting my data model to resolve the issue." Whenever you see lots of tables with the same fields, that sounds a warning bell. In this case, I think you can combine all of your separate followup tables into a single followup table linked to the Department dimension table through a department name (or preferably department code) field. That will simplify your data model five-fold, and should make the above problem very straightforward to solve.
Hi @lomorris, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
Brian,
I reworked my data model which was a huge step in the right direction. I have run into one problem that I have kind of hit the wall with. After redoing my data model, I quickly eliminated the multiple pages I had created down to 2 functional pages. The issue is this on my Survey Follow up page I have a table where I am calculating what I am calling aging. I’m just calculating the number of days between today and the Date Sent to Mangers field. What I am stumped with is the slicer I have added selects the department and filters correctly and the Table of transactions is dead on until I add a measure. any measure I add seems to kill the existing context and all the other rows show up as if the filter got wiped out. I am continuing to work it, but I thought I would ask for your assistance. Below are the before and after adding a measure screenshots.
You are SO close each time. In this case, your data model is dramatically improved. The one thing you forgot was to connect your data table to your new combined survey follow-up table. Once you do that, and adjust measure to turn on the relevant relationship, everything operates as expected per the screenshot below.
In addition while VALUE happens to give you the correct answer in this instance, the more appropriate function for your aging calculation is DATEDIFF. Finally, your Dates table needs to be marked as date table. With these changes, you should be good to go.
Surveys Aging by Days =
CALCULATE(
DATEDIFF(
[Date Sent To MGR],
TODAY(),
DAY
),
USERELATIONSHIP(
Dates[Date],
SurveyFollowUpTable[Sent to Manager]
)
)
Brian,
This worked as you said. I also incorporated your suggestion on using DATEDIFF. I am doing some additional clean up now, but all looks good so far. Thanks again for your support. Becoming more and more clear on how important getting your data model correct is.
