Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Dellis81
Continued Contributor
Continued Contributor

Single dimension with multiple columns in fact table

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.  Very similar to fact table w/multiple dates utilizing a single calendar table.   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, & Operating Center ID)

 

File link: https://drive.google.com/file/d/15IGfeE2isgA4AIE5q8olLZi7dv542sEe/view?usp=sharing

 

The below screen shot is an illustration of both the Dimension table (Center) and Fact table (Center).  Note the fact table has 3 references back to the Center Dimension

CDU1.PNG

I have three measures (utilizing the 'userelationship") dax function - and the below screenshot displays the correct result w/no external filters.   These are the correct totals.

CDU2.PNG

 

 

 

 

However, when I introduce external slicers/filters - I get a bunch of empty results.   In practice, I will have a page filter for Center Type "CC:Resources", with single select toggle for Center Group.   My request is return correct results when external filters/slicers are introduced.    The correct results would be as presented in 2nd screenshot image

CDU3.PNG

 

Thank you for assistance.   Yes, I have submitted same issue before, but I am hoping I have improved terminology and example  file.   Thank you!

4 REPLIES 4
parry2k
Super User
Super User

@Dellis81 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 Entry Type = "REsourceCDU1" and if you look at the raw data, when you select Center Type = CC:Resource, you don't have a record in the fact table where Entry Type = "ResourceCDU1" and that's why your measure doesn't return any value.

 

Quantity = calculate(sum(Data[Quantity]),Data[EntryType]="ResourceCDU1")

 

I hope it helps, at first look, it looks like a data issue, or maybe I missed something obvious, let me know.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Dellis81
Continued Contributor
Continued Contributor

@parry2k 
Yes, I see and understand what you are saying.   But, as usual - there is probably more to the story I failed to include.   For the data field "Entry Type" - there are numerous types that I use for other purposes.  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.   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.  But if you have thoughts on how these cost driver records could be tweaked, I'm open for ideas.    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.   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)

CDU4.PNG
Left matrix is the activity those resources (tractors, combines, etc)  were used in (within the same "GSF" entity (spraying, combining, planting, etc).

 

Right matrix is the operating entity and center for both GSF and GFF entities.

 

Please note the 18 hours tagged to Lease Center in left matrix matched the 18 hours represented under the GFF entity in right screenshot.  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!!!!


 

Hi @Dellis81 ,

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

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.   Hopefully this is better.

Link to file:  https://drive.google.com/file/d/14eRryX9Df_abgDkQepzUjYlPO5LFsLbj/view?usp=sharing

 

Within the datamodel - utilizing a quantity datafield with various entry types.   In this example - I am using a term "ResourceCDU1" - representing a cost driver unit for allocating costs.   I am also using other data entry types - for example ActivityCDU1, ActivityCDU2, HarvestUnits, etc.   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 relationship
Measures:
Quantity:  Simple base quantity measure, with no tag for "entry type".

Quantity = calculate(sum(Data[Quantity]))

ResourceCDU1 - utilizes the inactive relationship for Source CenterID.   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 relationship

Visual B = calculate([Quantity],Data[EntryType]="ResourceCDU1")

Visual C measure with an Entry Type "ResourceCDU1", and utilizes the inactive relationship with Oper CenterID

 

 

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.    My challenge - once i introduce a slicer or external filter - visuals B & C go blank.  The desired result is for all three visuals to return proper values within the same page (common slicers/filters).  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.   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 you

daryl

 

  

 

 

TryAgain2.PNG

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors