cancel
Showing results for
Did you mean:
Resolver II

## Calculated column based on two tables

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!

1 ACCEPTED SOLUTION
Resolver II

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

5 REPLIES 5
Resolver II

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

Resolver II

Hello everyone,

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

Super User

Hi @Jpbi94 ,

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.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Super User

Hi @Jpbi94

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Resolver II

Hi @MFelix ,

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

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!