Typical approach for this issues is below.
Make union duplicate data up to the number of the name in one cell.
Request_Union_Split.twbx 17.7 KB
@Shinichiro Murakami, thank you very much for your help. I was able to replicate this on my original data. I was wondering if you could explain what exactly we are doing to get that count.
This is to help others,
I was able to find a solution that suited my needs. My database has a huge number of rows and columns. The above-proposed solution would duplicate all the other columns in the database. This would pose a problem for me because some of the columns are supposed to be unique values. Also, I had 3 more columns that had multiple values in each row. Either would have to create separate databases for each sheet to analyze each of the columns. Also added newer records would not be easy. So here is what I did:
I created calculated fields for the columns that had multiple values for the row. 1 calculated field for every unique value. That way for Request I created 4 new columns- Request1, Request2, Request3, and Request4. Check for the substring and add 1 under the particular column if the value is present.
Drag the Measure values to Rows. Remove the measures you do not need from the Measure Values card.
By default, Measure Values had numbers with the decimal part. So I changed the format.
Then drag and drop the Measure Names into columns. It should automatically show up under Filter and Marks cards.
to improve the appearance I added color and the total number of the count on the sheet. Also added few other dimensions into the Filter card, which was a requirement for me.
Request- sample.twbx 22.4 KB