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

Question

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) contact_method(varchar) confirmed(True or False)

In this table, a user might have 0, 1, 2, or 3 confirmed contact methods.

I need to build a calcualted table - TABLE_NO_CONFIRMED - that contains all users that have 0 (none) confirmed contact methods.

I've begun chipping away by trying to simply filter on a count - but I'm missing how to roll up the count for each user. Here is my attempt that I know is incorrect, but at least it illiustrates my novice approach. Thanks for any input on how to do this:

(my approach which is of course not working): TABLE_NO_CONFIRMED = FILTER(‘db TABLE_A’,COUNTAX('db TABLE_A',COUNTAX('db TABLE_A','db TABLE_A'[confirmed] = TRUE())))

cahabadata · Answer

It would be easier for me to revert to SQL logic rather than DAX for something like this. I would:

a. created a calculated table: TableFalse that is just the false records

b. created a calculated table: TableTrue that is just the true records

c. do an outer join on the ID field of TableFalse to TableTrue so all False records result

.....in this resulting table some rows will have the TableTrue fields as blanks

d. create a calculated table: NoMethod that filters out the records with blank TableTrue fields (only 1 field is needed)

this might not get you quite there - - air code and haven't completely thought thru every possible variation.....but will get you close

am pretty sure I forgot to include Distinct.... you'll want 1 record per ID in each table true/false......

owenauger · Answer

Hi&nbsp;jkrewpbi&nbsp;Here are two ways I can think of to do what you just described, i.e. produce&nbsp;table containing campaign &amp; user_id for user_ids with no confirmed contact_method:&nbsp;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] )
    )
)

cahabadata · Answer

To clarify: you stateconfirmed(True or False)&nbsp;Then: "a user might have 0, 1, 2, or 3 confirmed contact methods"&nbsp;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?&nbsp;&nbsp;&nbsp;&nbsp;

jkrewpbi · Answer

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?".&nbsp;Almost - I need the list of users that have False for EVERY contact_method.&nbsp;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:&nbsp;TABLE_A:campaign&nbsp; user_id &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; contact_method&nbsp;&nbsp; confirmedX&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 123 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; email&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FalseX&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 123 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; text&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FalseX&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 321 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; email&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TrueX&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 321 &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; text&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; False&nbsp;So for my calculated table, I'm looking to get the following result:&nbsp;campaign&nbsp; userX&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 123&nbsp;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.&nbsp;&nbsp;

jkrewpbi · Answer

Thanks for your response. I'm evaluating your suggestion along with the above suggestion to split out the tables and peform a join.&nbsp;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.

Forum Discussion

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

9 Replies

Related content

Visual calculation- Contain

Contain Word

Generating Missing Data

Generating link

DAX: Combine CALCULATE and CONTAINS