cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jpbi94
Resolver II
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! 
 
Goals calculated column.jpg
1 ACCEPTED SOLUTION
Jpbi94
Resolver II
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

View solution in original post

5 REPLIES 5
Jpbi94
Resolver II
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

Jpbi94
Resolver II
Resolver II

Hello everyone, 


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

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])

        )  

MFelix_0-1657821059611.png

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



MFelix
Super User
Super User

Hi @Jpbi94 

 

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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!

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.