Single dimension with multiple columns in fact table

Question

Hello!I am needing help (please) with measures that have relate to multiple columns within the fact table, but single column within a dimension table.&nbsp; Very similar to fact table w/multiple dates utilizing a single calendar table.&nbsp; &nbsp;In this case, using cost accounting type data - have a center dimension, but the fact table has multiple center connotations (Center ID, Source Center ID, &amp; Operating Center ID)&nbsp;File link: https://drive.google.com/file/d/15IGfeE2isgA4AIE5q8olLZi7dv542sEe/view?usp=sharing&nbsp;The below screen shot is an illustration of both the Dimension table (Center) and Fact table (Center).&nbsp; Note the fact table has 3 references back to the Center DimensionI have three measures (utilizing the 'userelationship") dax function - and the below screenshot displays the correct result w/no external filters.&nbsp; &nbsp;These are the correct totals.&nbsp;&nbsp;&nbsp;&nbsp;However, when I introduce external slicers/filters - I get a bunch of empty results.&nbsp; &nbsp;In practice, I will have a page filter for Center Type "CC:Resources", with single select toggle for Center Group.&nbsp; &nbsp;My request is return correct results when external filters/slicers are introduced.&nbsp; &nbsp; The correct results would be as presented in 2nd screenshot image&nbsp;Thank you for assistance.&nbsp; &nbsp;Yes, I have submitted same issue before, but I am hoping I have improved terminology and example&nbsp; file.&nbsp; &nbsp;Thank you!

parry2k · Answer

Dellis81&nbsp;the reason your measures are returning blank is that you have a filter in your measure itself, let's take an example of the following measure, it is filtered where&nbsp;Entry Type = "REsourceCDU1" and if you look at the raw data, when you select&nbsp;Center Type = CC:Resource, you don't have a record in the fact table where&nbsp;Entry Type = "ResourceCDU1" and that's why your measure doesn't return any value.
&nbsp;
Quantity = calculate(sum(Data[Quantity]),Data[EntryType]="ResourceCDU1")
&nbsp;
I hope it helps, at first look, it looks like a data issue, or maybe I missed something obvious, let me know.
&nbsp;

dellis81 · Answer

parry2k&nbsp;Yes, I see and understand what you are saying.&nbsp; &nbsp;But, as usual - there is probably more to the story I failed to include.&nbsp; &nbsp;For the data field "Entry Type" - there are numerous types that I use for other purposes.&nbsp; For example - ResourceCDU1 is hours, ResourceCDU2 is acres, And then I have other CDU's for other center types (activities) that utilize the same "Quantity" data field.&nbsp; &nbsp;So, I will need some kind of filter to get to my ResourceCDU1's.In my 'bigger' data model, the provided model design seems to be working for 95% of the use cases.&nbsp; But if you have thoughts on how these cost driver records could be tweaked, I'm open for ideas.&nbsp; &nbsp; I just like a single record - as then you can see all three centers in one place.I have attempted another measure excluding the ResourceCDU1 filter - with same result.&nbsp; &nbsp;I was playing with DAX remove filters, and values functions with no success.Quantity (no CDU filter) = calculate(sum(Data[Quantity]),
    REMOVEFILTERS(Centers),VALUES(Centers[Center ID])If this helps (2nd and 3rd matrixes from initial explanation)Left matrix is the activity those resources (tractors, combines, etc)&nbsp; were used in (within the same "GSF" entity (spraying, combining, planting, etc).&nbsp;Right matrix is the operating entity and center for both GSF and GFF entities.&nbsp;Please note the 18 hours tagged to Lease Center in left matrix matched the 18 hours represented under the GFF entity in right screenshot.&nbsp; Those 18 hours are leased from the GSF to GFF entities, so I need differentiating center ID's data fields?Thank you for your help!!!!&nbsp;

anonymous · Answer

Hi&nbsp;Dellis81&nbsp;，
In order to provide you with a suitable solution, could you please give some simple examples of the results you want based on the available data in your sample pbix ifle? This needs to include the backend logic, calculation rules, screenshots of the results, etc. Thank you.
Best Regards

dellis81 · Answer

&nbsp;Thank you for responding - and as I was reviewing my explanation - realized how poorly explained it was for somebody not familar with my line of thinking.&nbsp; &nbsp;Hopefully this is better.Link to file:&nbsp;&nbsp;https://drive.google.com/file/d/14eRryX9Df_abgDkQepzUjYlPO5LFsLbj/view?usp=sharing&nbsp;Within the datamodel - utilizing a quantity datafield with various entry types.&nbsp; &nbsp;In this example - I am using a term "ResourceCDU1" - representing a cost driver unit for allocating costs.&nbsp; &nbsp;I am also using other data entry types - for example ActivityCDU1, ActivityCDU2, HarvestUnits, etc.&nbsp; &nbsp;Thus I need to maintain reference to ResourceCDU1 within our measures in this simplistic example.Secondly, I have a single "Center" dimension that is referenced 3 different ways in the Fact table (Center ID, Source CenterID, and Oper CenterID.Center ID's (circled in blue) represent the active relationship, those in red are tagged as inactive relationshipMeasures:Quantity:&nbsp; Simple base quantity measure, with no tag for "entry type".Quantity = calculate(sum(Data[Quantity]))ResourceCDU1 - utilizes the inactive relationship for Source CenterID.&nbsp; &nbsp;This is a key measure in multiple scenarios within the larger data model, and seems to be working well.ResourceCDU1 = calculate([Quantity], USERELATIONSHIP(Data[Source Center ID],Centers[Center ID]))For the three visuals I am hoping to get to work on same report page (with a common filter CenterID:CC:Resource - these are my three measures labeled by visual.Visual A = [ResourceCDU1]Visual B measure with an Entry Type "ResourceCDU1", but utilizes the active relationshipVisual B = calculate([Quantity],Data[EntryType]="ResourceCDU1")Visual C measure with an Entry Type "ResourceCDU1", and utilizes the inactive relationship with Oper CenterID&nbsp;&nbsp;Visual C = calculate([Quantity],Data[EntryType]="ResourceCDU1",USERELATIONSHIP(Data[Operating Center ID],Centers[Center ID]))The desired result is shown below using a common Center Type="CC:Resource" and Center slicer based on Center Group. I have pasted mockup Visual B and C inside the PBI image using "Sprayers" as a slicer variable.The measures provided seem to return a correct number - assuming no filters/slicers are in effect.&nbsp; &nbsp; My challenge - once i introduce a slicer or external filter - visuals B &amp; C go blank.&nbsp; The desired result is for all three visuals to return proper values within the same page (common slicers/filters).&nbsp; The above illustration is a mock up of what I expect with "Sprayers" are selected from the slicer.Thank you again, and hope I am becoming more clear and concise in my communication.&nbsp; &nbsp;Please let me know how to better communicate to help solve this problem, as it has become a major stumbling block in moving forward.thank youdaryl&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

Single dimension with multiple columns in fact table

4 Replies

Related content

RLS on multiple dimensions

Dimension columns for multiple values

Filtering across multiple dimensions

Sequential Order column based on multiple criteria

degenerate dimensions