cancel
Showing results 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.

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.

4 REPLIES 4
Community Support

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

here is pbix, please try it.

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.

Community Support

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

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

Announcements

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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors