Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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! 
 
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    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's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello everyone, 


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

    • MFelix's avatar
      MFelix
      Super User
      4 years ago

      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.