Forum Discussion
Grouping Data That Does Not Exist In The Table(s)
Hi Anonymous -
You could make a new Calculated Column in your table, which parses the portion of the string to group on. For example:
Parsed = MID(<TableName>[<ColumnName>], 7, 3)Then, add Parsed to a Slicer visual, if you want to filter a certain value. Or add Parsed to any visual, for that matter.Hope this helps,NathanI think you are headed in the right direction with a separate table. You could do 2 tables, one for odd and one for even or a combined (see below). You then just need to create the relationship with your other table and then use the fields from the new table as your slicer(s).
Value Category
001 Odd
002 Even
003 Odd
004 Even
I think jtownsend21 has a good start for your dimension table. Add as many descriptive attributes as you can think of, including the full code that is being parsed.
So yup....the answer is a combonation.
Just to follow up with what worked in it's entirety for the thread...
I found on the SharePoint where the data table exists of what all the possible options are and fetched that dataset as well.
In that table I parsed the field I needed parsed and grouped as I wanted.
Then I link the two tables together by the appropriate field and ka-boom! My reports show me what I was looking for.
Thanks to both of you guys for leading me down that path!! Awesome forum!
So just to be more clear.... the data I am looking at and want to group looks more like this...
K-ABC-001-DEF-JKL-MNO
or
K-ABC-002-DEF-JKL-MNO
It is the bolded, underlined data that I want to group. The data may or may not be currently in the dataset but has the possibility to be.
I have the list of possible options that could exist in the field.
Hi Anonymous -
You could make a new Calculated Column in your table, which parses the portion of the string to group on. For example:
Parsed = MID(<TableName>[<ColumnName>], 7, 3)Then, add Parsed to a Slicer visual, if you want to filter a certain value. Or add Parsed to any visual, for that matter.Hope this helps,NathanSo I get how to parse the data that currently exists in the table, what I don't understand how to do is create a group to include data that does not currently exist but could.
Im thinking I need to have a seperate linked table with all of the possible data options, parse it in that table and then link that table to the existing data set table?
I feel like Alice at this point.
Anonymous - Yes, it's always a good idea to separate "Dimensions" from "Fact Tables". If you're not familiar with "Dimensional Modeling", it would be good to get acquainted with that.
So yup....the answer is a combonation.
Just to follow up with what worked in it's entirety for the thread...
I found on the SharePoint where the data table exists of what all the possible options are and fetched that dataset as well.
In that table I parsed the field I needed parsed and grouped as I wanted.
Then I link the two tables together by the appropriate field and ka-boom! My reports show me what I was looking for.
Thanks to both of you guys for leading me down that path!! Awesome forum!