Data in separate tables is ok, after combining them in 1 table, one of the 2 data is incorrect

[Namoh]

Hi. I’m relatively new to PowerBi but slowly learning on the job.

I have the following issue.

I have three tables (more actually but for this problem these 3 are related):

• Calendar table
• Target table
• Actuals table

The Calendar table is linked to the Target table via a Date field, and the Calendar table is linked to the Actuals table via the same Date field (from table Calendar).

In table Target I’ve got a column with the facility names called: Werkeenheid.

In table Actuals I’ve got a column with the facility names called: ShortName.

I’ve created (with some help from here) a few DAX formulas to calculate my Actuals, Targets, YTD Actuals and YTD Targets.

When I put my Targets in a Matrix Table the numbers are ok, see figure 1.

When I put my Actuals in a(nother) Matrix Table those numbers are also ok, see figure 2.

But when I put them together in 1 Matrix Table my Actual numbers are not correct anymore, see figure 3.

The sum is working, but the numbers are incorrect.

My assumption is that his is caused because the Actuals don’t have any relationship with the field Werkeenheid.

When I want to create a relationship between Target.Werkeenheid and Actuals.ShortName (will be a many-to-many) I get a message, see figure 4.

When I de-activate the relationship between Targets and ID names (the bottom one) it still gives me this error (I did save in between), see figure 5?

 

Once I have these figures all in 1 table I’m going to use Conditional Formatting to colour the Actuals where they negatively differ from the Targets (≤+10% orange, >+10% red).

Below the (DAX) formulas for the Target and Actual figures.

If any other information is needed, please let me know.

Because of the complexity of the file and the classified information in it, I can’t provide a sample file.

_1_Total Downtime M = SUM ( Targets[Target Unplanned downtime (hrs)] )

_5_YTD_Target_Unplanned_Downtine =

var _result =

CALCULATE([_4_The first measure],FILTER(ALLSELECTED('CALENDAR'),'CALENDAR'[Date]<=MAX(Targets[Begin van de maand])))

return

IF(

    ISBLANK([_4_The first measure]),BLANK(),_result)

_1_Actual Unplanned Downtime (hrs) = SUM ( 'kpiexport'[Un-losses (hrs/month)] )

_5_YTD_Actual_Unplanned_Downtime M =

var _result =

CALCULATE([_4_The first measure actual],FILTER(ALLSELECTED('CALENDAR'),'CALENDAR'[Date]<=MAX(kpiexport[Month])))

return

IF(

    ISBLANK([_4_The first measure actual]),BLANK(),_result)

[Namoh]

Even after completely removing the relationship I get the same error.

It looks like PowerBi is mentioning a table but in reality the issue is with another table (bug?), because there's no link anymore between the two tables that is referred to in the (error) message.

The issue with my matrix table is probably because the Actuals don't have a link/relation with Werkeenheid.

They do have with ShortName, which is in principle the same as Werkeenheid, although not all values are present in both columns (Werkeenheid is a sub-set of ShortName).

It looks like something very simple that I'm missing, but I can't figure out the solution.

[Namoh]

As mentioned in my 1st post, I also want to add Conditional Formatting.

So I thought to try to add this to the two separate tables where the numbers are all ok.

But when I try to select the column to which the values should be compared, I can click what I want but it's not selected.

Again I think this has to do with the fact that Actuals and Targets both are linked to facilities, but that both are linked to a different column and that PowerBi can't link those 2 columns.

I hope all makes sense.

Anyone who might be able to help me a bit further?

[Namoh]

Is more information needed? Or haven't I explained the issue enough?

I'm really stuck at the moment.

[Namoh · Accepted solution]

I found the solution.

Because I had an inactive relations(hip), I had to make use of an userrelationship.

After adding

Actual Unplanned Downtime (hrs) = CALCULATE([_1_Actual Unplanned Downtime (hrs)],USERELATIONSHIP(Targets[Werkeenheid],kpiexport[ShortName]))

for

 _1_Actual Unplanned Downtime (hrs) = SUM ( 'kpiexport'[Un-losses (hrs/month)] )  

and adding 

YTD Actual Unplanned Downtime = CALCULATE([_5_YTD_Actual_Unplanned_Downtime M],USERELATIONSHIP(Targets[Werkeenheid],kpiexport[ShortName]))

for

_5_YTD_Actual_Unplanned_Downtime M =

var _result =

CALCULATE([_4_The first measure actual],FILTER(ALLSELECTED('CALENDAR'),'CALENDAR'[Date]<=MAX(kpiexport[Month])))

return

IF(

ISBLANK([_4_The first measure actual]),BLANK(),_result)

and use the two new measures in my (matrix) table, it worked.