Forum Discussion

jkrewpbi's avatar
jkrewpbi
Frequent Visitor
10 years ago

Generate calculated table containing rows where count of a value is greater 0 for each user

I know this is easy, but I can't see to find the right combination of steps to do what I'm trying to do.   I currently have TABLE_ALL that contains three columns (for example sake):  user(int) cont...
OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
10 years ago

Hi jkrewpbi

 

Here are two ways I can think of to do what you just described, i.e. produce table containing campaign & user_id for user_ids with no confirmed contact_method:

 

TABLE_NO_CONFIRMED_V1 = 
FILTER (
    SUMMARIZE ( TABLE_A, TABLE_A[campaign], TABLE_A[user_id] ),
    CALCULATE (
        COUNTROWS ( TABLE_A ),
        ALLEXCEPT ( TABLE_A, TABLE_A[user_id] ),
        TABLE_A[confirmed]
    )
        = 0
)
TABLE_NO_CONFIRMED_V2 = 
CALCULATETABLE (
    SUMMARIZE ( TABLE_A, TABLE_A[campaign], TABLE_A[user_id] ),
    EXCEPT (
        VALUES ( TABLE_A[user_id] ),
        CALCULATETABLE ( VALUES ( TABLE_A[user_id] ), TABLE_A[confirmed] )
    )
)
jkrewpbi's avatar
jkrewpbi
Frequent Visitor
10 years ago

Thanks for your response. I'm evaluating your suggestion along with the above suggestion to split out the tables and peform a join.

 

Both of the examples you provided produce results - running into some weirdness in that the calculated tables are not picking up some of the users that have values of False for all confirmation methods. Troubleshooting my backend data and how Summarize works, to see if perhaps users are being eliminated that belong to multiple notifications. Thanks again and I might have to follow up once I get a better handle on the pattern surrounding the rows I'm not seeing.

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    10 years ago
    No problem, there may have been something about the structure/interpretation of your full table that I didn't understand.
    For example, do you care about users on a per campaign basis?
    Anyway, get back in touch if needed.
    Owen ;)
    • jkrewpbi's avatar
      jkrewpbi
      Frequent Visitor
      10 years ago

      OwenAuger

       

      Playing around with the two examples you provided, I like your V1 option from a readiblity standpoint. Thanks so much!

       

      So I think I've nailed down my next issue to work through. For the visuals generated from the Calculated data set, I'm building a graph that slices the data based on campaign. For campaign X, show how many users did not confirm.

       

      So I've noticed that when I use a campaign slicer, the visual list of data from the calculated table changes but I can tell that the backend dataset is not. Took me a bit to figure it out, but now it makes sense as to why the sliced calculated column is "almost" correct. My guess is there is a way around this - otherwise a slicer against a calcuated table isn't much use.

       

      I have the option of changing the backend data, but I'm trying to avoid it if possible.