Display totals, by user, from two different tables, each of which needs filters

[mmace1]

Hi,

So I have two different tables, each of which has a listing of actions taken by users. 

Table A

User, Action Type

Table B

User, Action Type

I'd like to create a visualization that shows the total entries for each user, across both tables.  I also don't want the total, but to filter some of the "Action Types" out.

Visualization:

User, Count of usere  in both tables, when Action Type was 1, 4, or 7.

I'm already loading Table A & Table B seperatly for different reporting.

I thought of loading Table A a 2nd time, then loading Table B a 2nd time, and merging it with Table A.  But as I said, I'm already loading Table A & Table B for seperate things, and it seems kinda wasteful to reload both of them just for this.

Not sure I was coherent there, but if it made any sense - any ideas?  Thanks!

[CahabaData]

There is more than one way to accomplish what you seek - and it depends on what end result you seek.

In terms of Get Data (load); you don't have to re-do that.  If you wish to append the 2 tables together (into 1 long table) then this task is accomplished in the Query Editor.  It will perform that step each time you refresh.

If it is 1 long table - getting the count of actions is quite easy and not requiring any DAX - you can use the visuals - let's say a table visual; drag the action field into the values 2x and option the second one for count.  You can also apply visual level filters to remove the actions you don't want displayed.

If there is a compelling reason to keep the 2 table separate - then getting a sum of the 2 separate counts is going to require a Dax approach.

[mmace1]

Ah, I can't append the tables, becusae each table is actually a bit more complext.

Table A:

username, category code

Table B:

username, category code

There are then two seperate lookup tables that conver the category code to a description, and the two tables often use the same number to mean different things  So say category code 1 in Table A means "blah", category code 1 in Table B means "other thing".

So if I merged them, it wouldn't work, as those category codes (that I need to filter by) wouldn't be representing the same things, depending on which of the tables the original data came from.

So...dax formula?

[v-huizhn-msft · Accepted solution]

Hi @mmace1,

You can create a new table by clicking "New Table" under Modeling on Home page, type the following formula.

NewTable=UNION(TableA,TableB)

If you really want to identify where the data come from. At first, you can create a calculated column A in TableA as well as column B in TableB using the formulas below.

ColumnA="A"
ColumnB="B"

Then create new table using UNION function.

Best Regards,
Angelia

[mmace1]

Wow, I bet that work would really easily.  Thanks!