I have three tables in a data set. The first table has all the case information (Table Raw Data [ID, created date, closed date, asignee, name of ticket requestor, etc]. The second table has 2 of the same fields from the first - ID and closed date - but then has a lot of additional columns for office-specific metrics (Table Topic Data [topic category, Primary valueA, Secondary valueA, Tertiary ValueA, Primary valueB, secvalB, etc.].
The value columns in the second table could have drawn from the same list but not every entry has multiple values, so I used a Dinctinct calculation on a Union of the columns to show a list of all possible unique values from those columns.
ID | PrimevalA | SecvalA | TertvalA | closed date |
1 | Potato | Ice Cream | Salad | |
2 | Salad | Corndog | | |
3 | Cake | Pizza | Corndog | |
Table 2 above, after dictinct(union(distinct (TopicData[primevalA]), distinct(topicdata[secvalA]), distinct(topicdata[tertvalA]))) gave me the left column below
The right column was Var Count = Newtable[count]
var tbl1 = Union ( Selectcloumns(all(TopicData) "Notional Column", TopicData[primevalA]),
( Selectcloumns(all(TopicData) "Notional Column", TopicData[secvalA]),
( Selectcloumns(all(TopicData) "Notional Column", TopicData[tertvalA]))
var answer = countrows (filter (tabl1, [notional column] = count))
Return
Answer
Potato | 1 |
Ice Cream | 1 |
Salad | 2 |
Corndog | 2 |
Cake | 1 |
Pizza | 1 |
Fries | 1 |
Which works fine. I have a new table with two columns that tells me the unique values from my second table and how many times each option shows up across the entire Topic Data table.
However, on my dashboard I have 2 sections, one for new cases and one for closed cases. The new cases section is all data from Raw Data table, and the only thing in the closed case section is a bar chart of the newly calculated table with options and their frequency.
I'm trying to use the time slicer that's applied to the Raw Data table (currently set to slice by month) to also filter these option and frequency visuals, and I have no idea how to do that. I got a lot of help just getting this far. I can't unpivot my columns for a variety of reasons.
I'm hoping for a way to have PowerBI take the slicer that's applied to the Raw Data visual (currently slicing by month for Date Created) to also run against the Closed Date in the second Topic Data table, then only have the third calculated frequency table look at values within that range.
Any ideas appreciated.
