cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pokdbz
Helper II
Helper II

2 Fact table help

Fact 1

Store    Dept    Promo Flag

201       9          Y

202       9          N

 

Fact 2

Store   Dept    Sales

201      9          1000

202      9          2050

 

Dimension

Store, Dept, Promo Flag joined 1 to many

 

Incorrect Result I'm getting

Store    Dept    Sales    Promo

201       9          1000    Y

201       9          1000    N

 

I'm looking to get the result of

Store    Dept    Sales    Promo

201       9          1000    Y

 

I'm not sure why I'm getting duplicate rows with every value within the Promo Dimension.

Does anyone have any insights or ideas of whats going on or how to resolve this?

Thanks

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Hi, @pokdbz 

 

I think it is because Promo Flag Table is not filtering Fact2 table. (check the below picture's filtering direction)

 

You can try to write the measure for the Sales Total like below. Then it will work properly. But be careful when using crossfilter Function in DAX measure. In this case, because the model is not that complicated to understand, it should work properly.

Sales Total =
CALCULATE( SUM(Fact2[Sales]), CROSSFILTER( Fact1[Store], Store[Store],Both))

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

 

Picture10.png


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

@Jihwan_Kim 

Thanks for the help that worked.

If I wanted to add a 3rd fact table and again display the result of another dimension DSD_Flag.

Fact3

Store    Dept   DSD_Flag

201       9         Y

 

When I add the 3rd fact table its displaying the same row again with all values for DSD Flag

Looking to produce this.  

Store    Dept    Sales    Promo    DSD Flag

201       9          1000    Y             Y

201       9          1000    Y             N

 

Looking to produce this.  

Store    Dept    Sales    Promo    DSD Flag

201       9          1000    Y             Y

Hi,

I think you can try something like the below.

 

 

Sales Total =
CALCULATE (
SUM ( Fact2[Sales] ),
CROSSFILTER ( Fact1[Store], Store[Store], BOTH ),
CROSSFILTER ( Fact3[Dept], Dept[Dept], BOTH )
)
 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

@Jihwan_Kim 

Thanks for the help.  I think I've identified why its doing this.  But not sure what the difference is maybe you have some insight.

 

Table column aggregated value of Summarization of column SLS_AMT, it has the "E" next to it.  When I use this column it doesn't cause the row expansion

 

Measure created Sales = SUM(SLS_AMT)

When this measure is created is causes the extra rows to be produced.

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!