Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Histogram of questions answered by user/session/subscription

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.

 

 

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

hi, @Anonymous

I do a simple data model as  below:

basic databasic data

Step1:

Create an Interval grouping table 

6.PNG

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

7.PNG

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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.

 

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.