Get field value by individual query by row

[TSGD2123]

Hello,

Is it possible to get the values from a field by resolving individual queries for exch row?

Example: 

TABLE: (read from database)

ID    NAME   PURCHASED UDs

1     JOHN     select count(*) from table a

2     MARIE    select count( distinct uds) from table b

....

What I need is to get the return onf each query as PURCHASED_UDs value so that I get:

ID    NAME   PURCHASED UDs

1     JOHN     7

2     MARIE    9

....

Thanks a lot in advance!

[BA_Pete]

Hi @TSGD2123 ,

To implement this exactly how you're asking isn't really feasible, nor would it be efficient at all. I think the way to tackle it would be something like this:

Assuming there are three tables (ID/Name dimension, TableA, TableB), and that there is a common field among your tables (I'm going to assume [ID]) then you can send all three tables to the data model and relate the ID/Name dimension to both TableA and TableB on [ID].

Then you would create measures as follows:

_tableA_count = COUNTROWS(TableA)

_tableB_count = DISTINCTCOUNT(TableB[UDs])

_total_count = _tableA_count + _tableB_count

Once you have all this, you can put [ID] and [Name] from your dimension table into a table visual, then add whichever measure(s) show what you need them to.

Pete