Forum Discussion
Combining filters on multiple fields
So, after bashing my head against this for the best part of a month, I have finally come up with a somewhat 'cludgy' solution, that at least gives me what i want, and as usual - it was far simpler than i had been making it!
Ultimately - I have created a new column, which combines the status and issue type into a string - i am then using that as a filter for my graphs. It's not pretty, but it is proving functional, and allows me to filter down my fact table to show - for example - all stories at dev done AND all spikes at closed.
Please tell me if this measure is getting warmer. I'm still trying to understand your data. Please let me know which date you use on your x-axis. The sample data only had one row for each FromDate, so it is hard to do much. I just did a simple countrows but the same approach with the filter clauses in the calculates can be used with any measure.
VAR selectedstatuscountexcepttasks =
CALCULATE ( COUNTROWS ( Summary ), Summary[type] <> "Task" )
VAR tasksclosedcount =
CALCULATE (
COUNTROWS ( Summary ),
ALL ( Summary[to_status] ),
Summary[type] = "Task",
Summary[to_status] = "Closed"
)
RETURN
selectedstatuscountexcepttasks + tasksclosedcount
Hi Pat, thanks for that - I will give it a try in the morning and let you know how it goes. I assume by using a calculated field like that, I lose the ability to let the user view the records that make up the figure from the visualisation?
I'm using max_sprint_date as the X axis. The lines are built using fields from the issues table (which holds the issue metadata) that table links to the history and history_sprint_summary via the issue_key field.
Hi chipchidster.
Please change your data model to facilitate flexible calculations. You've got 2 different entities that you should not mix: Stories and Tasks. These need 2 different fact tables because they are very different. Do not try to sqeeze a round peg into a square hole (or the other way round).
You need a Date table, 2 fact tables as above and some dimensions. Some of them will be shared, some of them will not. You have to figure out which of them will be which and this depends on what you're trying to achive.
DAX is simple and fast on CORRECT models ONLY. Otherwise, you'll be creating some monster formulas that nobody else will understand... including you after 2 months. What's more, you'll never know if they return correct figures under all circumstances.
Please stay away from bad data modes as they'll bite you when you least expect it. Follow the good and time-honored rules of dimensional design and you'll save yourself a lot of grief and trouble.
Best
D