Forum Discussion

fsim's avatar
fsim
Icon for Responsive Resident rankResponsive Resident
8 years ago

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

 

Need Help

6 Replies

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Have you created relationships between your 5 tables?

    • fsim's avatar
      fsim
      Icon for Responsive Resident rankResponsive Resident
      8 years ago

      Hi  tex628 !

       

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

       

      • tex628's avatar
        tex628
        Icon for Community Champion rankCommunity Champion
        8 years ago

        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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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