Forum Discussion

TSGD2123's avatar
TSGD2123
Helper I
3 years ago

Get field value by individual query by row

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!

Need Help

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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