Calculated column based on two tables

[Anonymous]

Hi everyone, 

I want to make a calculated column based on two other tables. I need the date and the goal columns from both tables. From the first table (Eerste goal), I want the values for the column Goals when they are not blank, and when they are blank, and want a 0. I want different values from the second table (Stats_conc). I want a 0 for every row, so when the value for the column is Blank, 0, 1, 2, 3, 4, etc. I want a 0. That's why I said: Stats_conc[Goals]) = 99, since there is no player who ever will score 99 times.

The formule is as follows: 

Goals =

SWITCH(

TRUE(),

MAXX(

FILTER(

ALL('Eerste goal'),'Eerste goal'[Nieuwe Datum]=EARLIER('StatsEersteGoal'[Nieuwe Datum])

&&'Eerste goal'[Speler]=EARLIER('StatsEerstegoal'[Speler])),'Eerste goal'[Goals])<>BLANK(),

MAXX(FILTER(ALL('Eerste goal'),'Eerste goal'[Nieuwe Datum]=EARLIER('StatsEersteGoal'[Nieuwe Datum])

&&'Eerste goal'[Speler]=EARLIER('StatsEersteGoal'[Speler])),'Eerste goal'[Goals]),

MAXX(

FILTER(

ALL(Stats_conc), Stats_conc[Datum]=EARLIER(StatsEersteGoal[Nieuwe Datum])

&& Stats_conc[Speler]= EARLIER(StatsEersteGoal[Speler])), Stats_conc[Goals]) = 99,

MAXX(FILTER(ALL(Stats_conc), Stats_conc[Datum]=EARLIER(StatsEersteGoal[Nieuwe Datum])

&& Stats_conc[Speler]= EARLIER(StatsEersteGoal[Speler])), StatsEersteGoal[Goalss]),

0

)

Unfortunately, the result isn't what I expected. As you can see, there are blanks for several dates. Those dates can be found in the table Stats_conc. As a consequence, the measure Cumulative goals doesn't work. 
It's probably not needed but just for sure: the measure for Cumulative goals is this:

Cumulative goals =

CALCULATE(

SUM(StatsEersteGoal[Goalstest]),

ALL('StatsEersteGoal'),

'StatsEersteGoal'[Nieuwe Datum]<=

MAX('StatsEersteGoal'[Nieuwe Datum])

&&'StatsEersteGoal'[Speler]=MAX('StatsEersteGoal'[Speler])

) + 0

I hope anyone can help me out to combine those two tables. I more information is required, just let me know! 

[Anonymous · Accepted solution]

Hi @MFelix 

Thanks for your reply. Sorry for my late response, but I couldn't move on with my project, since I was busy with a new project. 

Unfortunately, it wasn't the solution I was looking for. So, I approaced it differently. Instead of making one table with a complex calculated column, I kept the two tables seperatly, but with all the dates. For all those new dates which were filled with blanks, I replaced those blanks with 0's. So, instead solving the blanks by creating a calculated columns, it's solved in the Query Editor, which, in this case, was more intuitive. 

I created a bridge table to link the two table (and to have a relationship from one-to-many). 

Thanks for your help @MFelix , and I am happy it's finally solved

[Anonymous]

Hello everyone, 

The issue is still not solved. Could someone help me out?

[MFelix]

Hi @Anonymous ,

Sorry for not giving you an answer before believe I have miss the notification about the message, please apologize me and be sure it was not on purpose.

I looked at your file, and since you have a date table relate with your tables you need to make the cumulative based on that value so if you change your metric to the following believe this solves your issue:

Cumulative goalstest = 
CALCULATE(
    SUM(StatsFirstGoal[Goalstest]), 
    ALL('StatsFirstGoal'),
        'StatsFirstGoal'[NewDate]<=
        MAX('Date'[Date])
        &&'StatsFirstGoal'[Speler]=MAX('StatsFirstGoal'[Speler])

        )  

How you can see what I did was to change the MAX('StatsFirstGoal'[NewDate]) by the MAX('Date'[Date]).

Is this the result you need?

Be advise that your Calendar table should be continuos and not jumop dates in order to have a proper calculation of time intelligence values.

Check PBIX file attach and once more accept my apollogies.

[MFelix]

Hi @Anonymous 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Anonymous]

Hi @MFelix ,

Thank you for your reply!

It's open data, so here is the file:

https://drive.google.com/file/d/1wi7RVHpbOQenvSixhTcCmM_3V-1EuXWy/view?usp=sharing

I tried to explain the problem in more depth in the file, also by including visuals of course. I hope this helps, and otherwise, let me know!