cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jkrewpbi Frequent Visitor
Frequent Visitor

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) 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())))

 

 

9 REPLIES 9
CahabaData New Contributor
New Contributor

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

To clarify: you state

confirmed(True or False) 

Then: "a user might have 0, 1, 2, or 3 confirmed contact methods"

 

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?

 

 

 

 

www.CahabaData.com
jkrewpbi Frequent Visitor
Frequent Visitor

Re: 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.

 

 

CahabaData New Contributor
New Contributor

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

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......

 

 

 

 

 

www.CahabaData.com
OwenAuger Super Contributor
Super Contributor

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

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] )
    )
)


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




jkrewpbi Frequent Visitor
Frequent Visitor

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

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 Super Contributor
Super Contributor

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

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 Smiley Wink


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




jkrewpbi Frequent Visitor
Frequent Visitor

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

@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.

jkrewpbi Frequent Visitor
Frequent Visitor

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

 

I've created two tables with something like the following:

 

calc_conf_false = FILTER('TABLE_A','TABLE_A'[confirmed]=FALSE())

calc_conf_false = FILTER('TABLE_A','TABLE_A'[confirmed]=TRUE())

 

But when I attempt to perform a left outer join I get an 'No common join columns detected' error. They have the same number of columns.

 

calc_joined_conf_falsetrue = NATURALLEFTOUTERJOIN(calc_conf_false,calc_conf_true)

 

Now here's where I'm treading into newish territory. I can do this via SQL but I haven't created a join as you mentioned in PowerBI before, so I'm not quite certain how it's automatically trying to join these tables togther.

 

I'm tackling both approaches on this thread since I can see cases where I need to break out data in Power BI that comes from non-relational structures. So in this case, I can see breaking out multiple tables from one. Am I totally on the wrong path the way I'm creating the calculated tables to begin with, perhaps?

Moderator v-caliao-msft
Moderator

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

Hi @jkrewpbi,

 

Maybe you needn't create additional table in your report, you can add some calculation in your table. I have tested it on my local environment, you can refer to the calculations below.

 

Calculated column:

Confirmed_0 = IF(TABLE_ALL[Contact_Method_0]="False",0,1)Total = CALCULATE(SUM(TABLE_ALL[Confirmed_0]),ALLEXCEPT(TABLE_ALL,TABLE_ALL[Campaign],TABLE_ALL[User_ID]))Total2 = CALCULATE(DISTINCTCOUNT(TABLE_ALL[User_ID]),FILTER(ALLEXCEPT(TABLE_ALL,TABLE_ALL[Campaign]),TABLE_ALL[Total]=0))

 

Measure:

Measure = CALCULATE(SUM(TABLE_ALL[Confirmed_0]),ALLEXCEPT(TABLE_ALL,TABLE_ALL[Campaign],TABLE_ALL[User_ID]))

 

Create a table looks like below:
Capture.PNGCapture1.PNGCapture2.PNG

Regards,

Charlie Liao

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 277 members 2,951 guests
Please welcome our newest community members: