Invoke multiple information from other columns

[Anonymous]

Hello Community!

I get a little bit of truble here:

Got 3 tables with this information as an example:

Table 1:

User    Team         Completed

User1  Team1         1

User2   Team1        0

User 3  Team1        3

Table 2:

User Team Completed

User 1  Team2   3

User2 Team2     4

User3 Team2    0

User Team Completed

User 1  Team3   5

User2 Team3     8

User3 Team3     3

So i need Help to show in a visualization the total completed of each team with a selector that allows me to select the team and a number card to show me all the completed SUM.

i think that maybe creating a "symbolic" table to relation with all the completed and team information would allow me to retribe the requiered information in a visualization but i can't do it.

Thank you! 🙂 

[TomMartens · Accepted solution]

Hey,

basically I'm wondering why you have multiple table.

From within PowerQuery I personally would appende the tables, so that I finally have just one.

Does this makes sense?

Regards

Tom

[Anonymous · Accepted solution]

Do UNION ( Go to Modelling-> New Table-> create a new DAX table using the UNION) of all 3 tables using DAX, so you will have all details in the DAX table. Then you can create a measure SUM(DAT_Table[Completed]) and pull this measure and team column in visuals.

Also you can use this measure in a Card as needed.

Thanks

Raj

[Anonymous · Accepted solution]

Do UNION ( Go to Modelling-> New Table-> create a new DAX table using the UNION) of all 3 tables using DAX, so you will have all details in the DAX table. Then you can create a measure SUM(DAT_Table[Completed]) and pull this measure and team column in visuals.

Also you can use this measure in a Card as needed.

Thanks

Raj

[TomMartens · Accepted solution]

Hey,

basically I'm wondering why you have multiple table.

From within PowerQuery I personally would appende the tables, so that I finally have just one.

Does this makes sense?

Regards

Tom

[Anonymous]

Yes, it would be the best i guess, 

But when i append all the tables i get this error:

" Failed to save modifications to the server. Error returned: ' OLE DB or ODBC error:{DataFormat.Error} Too many fields defined..'. "

I'm getting my data set from an access db file. But i thinked that the Queries modeling in powerBI was storaged in the local file. Don't understand why this is traying to save the appened file in a database.

Do you know why this is happening? :S

Thank you!

[Anonymous · Accepted solution]

Not sure whether you are doing this in Power Query or in DAX.

If you do in DAX - Yes, all modelling/calculations happen in Power BI inmemory.

If you get this error hile doing UNION in DAX, please share the snapshot of your formula. Something you are missing .

Thanks

Raj

[Anonymous]

Yes, 

Creating the Union formula to join all tabs and then the DAX SUM , was the  solution.

Thank you Raj

[Anonymous]

HI Raj,

I was using the first option, PowerQuery.. 

Edit Querys -> Append Queries --> Append queries as a new.

don't know why i got this ODBC error :S , 

Gonna try with DAX union, as you say to me before.