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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fsim
Responsive Resident
Responsive Resident

how to create a table with data (counts) comming from differents tables ?

hello !

 

here is the problem I cannot solve alone 🙂

 

We ran  4 surveys with different question grouped by categories. The results are in 4 diffrent unpivoted tables.

for each of these 4 table , I added a new measure that gives the distinctcount of the answers (called suervey_1_counter in the table1... and so on)

 

I also have a table with the name of the survey and the number of sending

 survey_1  50

survey_2 300

survey_3 85

survey_4 6

 

And now, I want to know what is the % of answers we received for each survey.  So I'd like to add my distinctcount measures of the 4 different tables  in a second colum next to the number of sending

I  found a function called evaluate thats should do the job; but I don't understand where to use it 😞 

something like evaluate {"surevy_1_counter";"survey_2_counter...)

 

can someone help me ? thank you in advance

 

Fred

 

6 REPLIES 6
v-yulgu-msft
Employee
Employee

Hi @fsim,

 

 

Create a calculated column in that table which contains name of the survey and the number of sending. Here, [DistinctcountforSurvey1] is the measure you created to calculated the distinct count for Survey 1.

distinctcount per survey =
IF (
    Table5[Survey] = "survey_1",
    [DistinctcountforSurvey1],
    IF (
        Table5[Survey] = "survey_2",
        [DistinctcountforSurvey2],
        IF (
            Table5[Survey] = "survey_3",
            [DistinctcountforSurvey3],
            [DistinctcountforSurvey4]
        )
    )
)

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
tex628
Community Champion
Community Champion

Have you created relationships between your 5 tables?


Connect on LinkedIn
fsim
Responsive Resident
Responsive Resident

Hi  tex628 !

 

there are no relationship between the tables as they are completely independant (different subjects in the surveys)

 

tex628
Community Champion
Community Champion

Well they need to be connected for the measure to work in the same table. Do a many to one relationship from all the large tables to the small table that you created. 

 

Create a column in the survey_1 table that writes "Survey_1" in each row.

 

Create a relationship between this column and the column in your small table holding "survey_1" etc.

 

Hope this helps! 

BR,

J


Connect on LinkedIn
fsim
Responsive Resident
Responsive Resident

I was too fast to mark it as a solution. This solution gives me four column with the count of responses in every survey. Not one column with the four counters . Did I miss something ?

 

Best

Fred

 

tex628
Community Champion
Community Champion

My initial response only showed part of the solution so dont worry! 🙂 I'll provide some images showing what i had in mind!

I have 3 surveys, and 1 connecting table.I have 3 surveys, and 1 connecting table.Each survey looks like this. With different amounts of differentiating answers.Each survey looks like this. With different amounts of differentiating answers.

Note the "Survey" column in the survey 1 table. This is simply:

Survey = "Survey 1"

I have one of these in each table, this allows me to create a relationship like this:

Connection between the survey columns and the "Surveys" column in the master table.Connection between the survey columns and the "Surveys" column in the master table.
When this is connected properly i create two measures. They look like this:

# of questions = CALCULATE(COUNTROWS('Survey 1')+COUNTROWS('Survey 2')+COUNTROWS('Survey 3'))

 

Distinct answers = DISTINCTCOUNT('Survey 1'[Answers])+DISTINCTCOUNT('Survey 2'[Answers])+DISTINCTCOUNT('Survey 3'[Answers])

Which results in this table:

image.png

Hope this helps! 🙂


Connect on LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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