cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mgrayTCB
Helper III
Helper III

Measure solution instead of a column. Multiple fact tables.

I have been away from Power Bi for a while and thought this would be easy but I am stuck (probably forgetting something basic). My model looks like this

 

mgrayTCB_0-1670007223896.png

 

 

I am trying to create a table that lists projects, one particular milestone date, and all the equity records for the associated projects.

My measure to find the particular milestone date works fine against projects but as soon as I add equity I end up with what looks like an outer join all equity records against all milestones and lots of blanks.

My milestone measure is:

DateClosingMeasure =

CALCULATE(

maxx(Milestones,

Milestones[DateActOrEst]),

Milestones[Milestone] = "initial closing")

 

Im trying to get a table visual that looks like this:

mgrayTCB_1-1670007223899.png

 



I keep getting:

mgrayTCB_2-1670007223902.png

 

 

I am trying to do it only with measures. It seems like I can make it work if I add a column to the project table and calculate the milestone date but I feel like I should be able to do this only with measures.

 

Any help pointing me in the right direciton woudl be great.

3 REPLIES 3
lbendlin
Super User
Super User

Look at your data model and ask yourself "Can I get there from here?"  You have a dimension table that controls three fact tables.  That also means you cannot control one fact table from another fact table - hence the crossjoin. 

 

Not saying that is a bad thing.  But sometimes the data model needs to be adjusted to the business question.

Im not tying to control filter one fact table from the other. I am just trying to report two different facts against the project. For every project select one milestone date and report the multiple equity records assocated with that project. The milestone date and project name can repeat for each equity record. 

 

I am simply trying to use power bi to extract some related data from mulitple tables.

Make sure that the dimension fields in the visual are coming from the dimension table.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.