cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nmck86 Member
Member

Calculated Column Help

Hello! I was hoping I could ask for your help on a query issue I have ran up against. I put this on the forum but thought I would ask any SMEs in Power BI I knew as well.

 

I have the attached pbix file and a BU TYPE 2 column that I am trying to pull in the area from a different table. Long story short I am trying to get the 2 visuals below to have matching totals across each month. One is for BU Type and the other even though it is titled BU Type 2 it is really AREA. Essentially I need my bottom graph to match the top one except using the EA, FA, Other or blank options.

 

Screen Shot 2018-08-06 at 10.12.36 PM.pngScreen Shot 2018-08-06 at 10.12.23 PM.png

 

I would love to get my BU2 or my BU Type 2 fields to work correctly and match from a total perspective with the top chart. Any help would be amazing. The pbix file is below.

 

 https://drive.google.com/file/d/1PvVODz4RZ6NyihKiVK32WkPUE_ysIiOo/view?usp=sharing

3 REPLIES 3
Seward12533 New Contributor
New Contributor

Re: Calculated Column Help

Spent a few minutes looking at your file and honestly I think your overcomplicating things and trying to force a paradigmn of working in classic excel into PowerBI.  Can you help me understand the purpose of the Defects file and why your manually calculating all these related fields and why so many "VLOOKUPS" (i.e. LOOKUPVALUE) rather than relying on relationships and letting PowerBI do the work for you? Since everything is in the Defects Table is based on the running file with lookups from other tables. 

 

If getting your BU2 lookup in the Defects table to work will help you make this work but the problem is that  BU2 is not unique to the index you built for these values.  If this is a bug and can be fixed you can use a table of BU2 to Material and Period.  To test I used a table created this DAX 

BU2s = SUMMARIZE('Defects Running File','Defects Running File'[Material and Period],"BU 2",FIRSTNONBLANK('Defects Running File'[BU2],1),"Num BU2s",COUNTROWS(VALUES('Defects Running File'[BU2])))

 

Material and Period        BU 2      Num BU2s[

PN-215803|7/1/2018FA2
PN-215811|7/1/2018FA2
PN-215808|7/1/2018FA2
PN-215816|7/1/2018FA2
PN-215791|7/1/2018FA2
PN-111664|7/1/2018FA2
PN-193343|7/1/2018FA2

 

 

It seems like your compensating for not getting the values you want via the relationships because you dind't know how to get the filter context applied correctly. 

 

First thing to understand when relating tables via bridge and lookup tables that by default the relationships wont apply against the arrows and as I'm sure you figured out you can only set so many Bi-Directional relationship (both ways) to overcome this without getting into conflicts. But you can use DAX to specify when you want to crossfilter when you need it. 

 

To help visualize this I remapped your relationship putting the tables on the 1 side of the relationship and the many (*) side on the bottom.  The filter context wont go up and over by default. (see below)

 

This is a common problem I have found when mentoring people who are new to thinking in the relational framework of powerbi. I put together a tutorial for an internal user group we have. https://1drv.ms/u/s!AuCIkLeqFmlhhJgzLZ6jPcWgLGu3Yw

 

capture20180808212426947.png

 

 

 

 

 

 

nmck86 Member
Member

Re: Calculated Column Help

Thanks a ton! I was able to apply a filter and get the results to somewhat work. This not my work :-( I have told this person that the use of so many calculated columns is not the best method for building out Power BI reports and dashboards and the complexity that was built is extremely unncessary. However, the setup is complete and I believe they will be migrating off Power BI potentially in the near future so I am trying to get them through a mess that someone else created. I just couldn't seem to figure out how to assist at all at first. However, I may have figured out something in the interim and I highly suggested to them that I recreate the entire file structure so that most of those calculations can be removed from or modified if applicable to measures. I appreciate your assistance and I will try and see if your recommendations make what I did work better. In the meantime, I simply used the EA, FA, Other fields as a slicer so that you can filter it that way. I think the calculated columns are creating some filtering automatically that I didn't want. Difficult to come in and fix someone elses mess but wanted to help a friend in need. 

Seward12533 New Contributor
New Contributor

Re: Calculated Column Help

Too bad they missaplied a great tool and will be moving off it to something potentially inferior or more costly and most like less flexible and powerful.