Creating a column to calculate missing values in another column

[teresab123]

Greetings!

I'm building my latest report in PBI and trying to do the following : 

I have a value column where the data is incomplete. So I figured I would add a calculated column to calculate the missing values for my visuals. However, it's not going well and I don't know why the Related function is not working. It is on the 1-side of the 1-Many join. Here's the scenario (tables/fields masked for client confidentiality):

Table 1 -- Main table with many values ;

Table 2 -- Estimate Rate lookup table with the values I need to use in the calcluation

Tables are joined by a special key field I created to make the join between the excel sources. But I made sure the direction is Table 2 (1) ---> Table 1 (Many) in keeping the join simple. 

There is a third table, which is a many to many but it is not used in the calculation. But I have set it one direction only such that it works as a 1 to many. My curent table visuals I've already created appear so far to be valid (still confirming). 

I'm building the calculated column in Table 1. Here's my formula that I have built that I can't get the calculation to work :

MissingValueColumn =
VAR RateEstimate = Table1[Total_Perc50] * RELATED(Table2[ttl_Perc10_est_rate])

    RETURN

IF( ISBLANK(Table1[Total_Perc10]), RateEstimate, Table1[Ttl_Perc10] )

Any/All help would be appreciated !!!

--Teresa.

[CNENFRNL]

Hi, @teresab123 , based on your description, the formula appears normal to me. Is there any error prompt or unexpected calculation values?

[teresab123]

Hi @CNENFRNL ,

I did get errors this morning but through working out morning syntax issues (ie typing, not enough caffeine, etc), the current formula is processing without any errors thrown. It's just not bring back any results for the calculation. The false portion of the IF statement is working as expected. But my calculated column is simply a duplicate of the original value column. I know this doesn't help but I can provide alittle more context :

In the rate table, there are unique values for a specific position-level (there are about 25 unique values which have their own unique rates. Each level has 4 different rates depending on missing value(s)). I had joined the rate table to the main table via this unique field with the intention to fetch the rates for those missing values. So I'm not sure why the calculation is simply not working. Now that I am thinking about it, I may do a drill down table to see if the join is actually working . See if I can simply pull the rates into detail table. But any help would be appreciated.  I'm confused as to why the variable formula appears to not be working.!

[CNENFRNL]

@teresab123 , if you share your pbix with some mockup data, it would be much easier to troubleshoot then.

[teresab123]

hi @CNENFRNL 

I would agree to that , but that would take me some time to prepare. But I just did what I thought of in my earlier response to you and it appears that the join is not working. While I can create a table visual against table 2 on a report page and view the rates, when I try to do the same using the co-joined field from table 1, all the rates disappear from the visual. So I'm not going too crazy - I thought I could still build DAX formulas after a long layoff - but now this tells me the M-2-M link (which I have set to single directional) between table 1 and table 3 must be impacting the direct relationship between table 1 and table 2.  Let me know how you would like me to send a test file over as I appreciate the offer to troubleshoot. 

[CNENFRNL]

Hi, @teresab123 , to my understanding, your current issue with M2M between table 1 and 3 weighs more than that RELATED issue, isn't it? As to me, I always try to avoid M2M at all costs, as many data gurus advised.

Better that you share a pbix file with a full data model and some dummy data so that we could investigate into a practical senario and seek some solution together.

[teresab123]

I'm working on a mocked up file now. Please advise as to how to send you the file. I'm not getting emailed on this thread for some reason.