Histogram of questions answered by user/session/subscription

[Anonymous]

Hello.

I have 3 tables, they contain a lot of data but for what I need I believe the folllowing columns are what I need.

Table 1 contains SessionID (non unique), QuestionID (non unique), QuestionAnswered (TRUE or FALSE)

Table 2 contains SessionID (unique), SubscriptionID (non unique)

Table 3 contains SubscriptionID (unique), MemberID (non unique), SubscriptionName (non unique)

They are related through Table1.SessionID->Table2.SessionID and Table2.SubscriptionID->Table3.SubscriptionID.

A user (MemberID) can have multiple subscriptions for the same or even different products (SubscriptionName) and each subscription can have multiple sessions where the user answered questions.

What I need is to plot the distribution (I guess I need to make 3 histograms) of how many questions are being answered by session, by subscription and by user.

For example, if I have 5 users which have answered each 5,8,15,21,34 questions in total then I want a graph with let's say bins of ten questions answered so (0-9,10-19,20-29,30-39) and then bin 0-9 will have frequency 2, bin 10-19 frequency 15, etc.

Can you help me? I managed to get a questions answered per session histogram but I couldn't filter it based on if the QuestionAnswered = TRUE so it showed every question attempted.

I can easily make a matrix that shows the numbers I need to use ( questions answered per user/session/subscription) but I don't know how to make the histogram. I guess I need to extract these numbers to a new table or column but I am new with DAX so it is pretty complicated.

Thanks for your help.

[v-lili6-msft]

hi, @Anonymous

I do a simple data model as  below:

basic data

Step1:

Create an Interval grouping table 

Step2:

Create this measure as below:

Measure = CALCULATE(COUNT(Table1[ID]),FILTER(Table1,Table1[times]>=MAX(Table2[start])&&Table1[times]<=MAX(Table2[end])))

Step3:

Drag field group into Axis and measure into visual

hope this can help you 

here is pbix, please try it.

https://www.dropbox.com/s/ur3vxzxtbm0z6xy/Histogram%20of%20questions%20answered.pbix?dl=0

If it is not your case, please share your simple samp pbix or some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading

Best Regards,

Lin

[Anonymous]

Thank you for your help but my main problem is that I don't know how to calculate the number of answers when QuestionAnswered = TRUE for each MemberID/SubscriptionID/SessionID. So in the table you made I don't know how to calculate the 5,8,15,21,34. 

I have managed to create a new table with unique SessionID and how many questions where answered for each but could not filter only for QuestionAnswered = TRUE. Probably it's something using the functions calculate,countrows,filter etc. but it's way too advanced for my current DAX level.

[v-lili6-msft]

hi, @Anonymous

It should be able to be filtered by slicer, we need to look at the data model structure. Could you please share us some simple sample data and the expected output?

Best Regards,

Lin

[Anonymous]

Thanks for replying @v-lili6-msft

Here is a file with a simplified data model structure.

Basically what I need is to create tables from my existing data like the ones shown on the report page which can be filtered by using slicers or page filters. If I get these I can then find out how to create the histograms.

https://www.dropbox.com/s/r1j6nrvyazmwlcn/Data%20Model.pbix?dl=0