Forum Discussion
Generate calculated table containing rows where count of a value is greater 0 for each user
For your question "so then it would be a record count? (since the field is true/false) meaning that if 0 contacts you want to count False and only false?".
Almost - I need the list of users that have False for EVERY contact_method.
Another way to look at it (I've added the campaign column in this example - just for context since this table is tied to others via the campaign:
TABLE_A:
campaign user_id contact_method confirmed
X 123 email False
X 123 text False
X 321 email True
X 321 text False
So for my calculated table, I'm looking to get the following result:
campaign user
X 123
I felt it would be kind of odd to stick a calculated column in TABLE_A, because whle the data exists in TABLE_A to determine which users did not confirm at all, I tend to think of keeping calculated/summary data separate from non calculated.
Super UserHi 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] )
)
)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.
- OwenAugerNo 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 ;)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.