The disadvantage of the 3rd solution is that it uses a larger dimension table, with a more granular user key in the fact table. Quantity Measure 2 = SUM(Table2)Īll 3 of these solutions produce the results you are looking for - you will need to consider / test the trade-offs between them. It will need to be a Many-to-Many relationship, with User Slicer filtering the fact table. Create a Relationship between your fact table and User Slicer table, on the User Combo column. Create a new Calculated Column on your fact table: Create the modified User Slicer Calculated Table:ĭISTINCT(SELECTCOLUMNS(Table2,"User", "User Combo", & "->" & )),ĭISTINCT(SELECTCOLUMNS(Table2,"User", "User Combo", & "->" & ))Ģ. There is a 3rd solution as well, which would create a larger User table instead of duplicating rows in the fact table and also create a natural relationship/filter:ġ. Duplicate rows means that the fact table will only produce the correct results if you apply a filter. solution contains duplicate rows, but creates a natural relationship/filter. My solution does not duplicate rows in the fact table, but it creates a non-intuitive (and likely slow) filter.Ģ. Re-structuring the database is an option, but I would like to avoid that if possible.- There are pros/cons to each of the 2 solutions:ġ. I can do this pretty easily with multiple pivot tables and charts linked to the same data, but it would be cool if I could do this all on one chart / table. Where I am getting stuck is figuring out how to slice the chart so that I can quickly see just cell density, or viability, etc. Using a pivot table and pivot chart I was able to easily build a chart that automatically updates as new data is added, and that I can easily slice using the Batch ID. I also want to be able to easily slice the data on the chart to see different batches or different sample values. My goal is to make a chart that requires 0 maintenance once new samples are loaded into the database. Each sample has a few different measures against it that are all plugged into a table (example below). I have a sample database that contains many daily samples for multiple batches. They are identifiable with a special user flair.Ī community since MaAsking a question? Describe if you are using Excel (include version and operating system!), Google Sheets, or another spreadsheet application. Occasionally Microsoft developers will post or comment. Recent ClippyPoint Milestones !Ĭongratulations and thank you to these contributors Date Include a screenshot, use the tableit website, or use the ExcelToReddit converter (courtesy of u/tirlibibi17) to present your data. NOTE: For VBA, you can select code in your VBA window, press Tab, then copy and paste that into your post or comment. To keep Reddit from mangling your formulas and other code, display it using inline-code or put it in a code-block This will award the user a ClippyPoint and change the post's flair to solved. OPs can (and should) reply to any solutions with: Solution Verified Only text posts are accepted you can have images in Text posts. Use the appropriate flair for non-questions.Post titles must be specific to your problem.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |