Forum Discussion

Dpit's avatar
Dpit
New Member
3 years ago
Solved

dax calculation

 

Hello


I'm contacting you because I can't get a DAX formula related to my needs
these my issue :

I have, in my datasource 3 columns
my table aims to analyze answers to survey questions

I have, in my source, 4 main columns


ID_PERSON (person who responded)
QUESTION_ID (
ANSWER (answer to the question)
RATIO ( ratio for person)
FYI I have about 100 questions and 5000 people

Example :

ID_PERSON,ID_QUESTION,ANSWER
1,1,yes,ratio_person
1,2,OK,0,65
1,3,Problem,0,65
2,1,No,0,65
2,2 OK,0,65
2,3, Trouble
3,1,No,0,65
3,2 OK,0,65
3,3, Trouble,0,65
3,1,No,0,65
3,2 OK,0,65
3,3,No Problem,0,65


In my case My objective is to know that the people who answered Yes to question "x" (user can choose), answered question 1,3,... (i have 20 question preselected)

Example here, I have 2 people who answered "No" to question 1, they answered 100% OK to question Number "1"

==> Result
Trouble = 66%
NO Problem = 33%


in my formula, I would therefore have to retrieve, depending on the answer chosen to a question (Step 1), the number of answers to question 2 (fixed ID) only for people who answered what we are looking for


I try to explain it with un screen..

i've tried whey many compute but i didn't find it 😞 could you help me?) thank a lot

 

Need Help
  • AmiraBedh's avatar
    AmiraBedh
    3 years ago

    If possible, please share your pbix file, otherwise try the following :

     

    AnswerDistribution =
VAR SelectedQuestion = 1 -- Replace with the question ID you want to filter
VAR SelectedAnswer = "No" -- Replace with the answer you want to filter
VAR FilteredPeople =
CALCULATETABLE(
VALUES('Survey'[ID_PERSON]),
'Survey'[ID_QUESTION] = SelectedQuestion,
'Survey'[ANSWER] = SelectedAnswer
)
VAR QuestionToAnalyze = 2 -- Replace with the question ID you want to analyze
VAR Result =
CALCULATE(
COUNT('Survey'[ANSWER]),
FilteredPeople,
'Survey'[ID_QUESTION] = QuestionToAnalyze
)
RETURN
IF(Result > 0, Result / COUNTROWS(FilteredPeople), BLANK())

2 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    3 years ago

    If possible, please share your pbix file, otherwise try the following :

     

    AnswerDistribution =
VAR SelectedQuestion = 1 -- Replace with the question ID you want to filter
VAR SelectedAnswer = "No" -- Replace with the answer you want to filter
VAR FilteredPeople =
CALCULATETABLE(
VALUES('Survey'[ID_PERSON]),
'Survey'[ID_QUESTION] = SelectedQuestion,
'Survey'[ANSWER] = SelectedAnswer
)
VAR QuestionToAnalyze = 2 -- Replace with the question ID you want to analyze
VAR Result =
CALCULATE(
COUNT('Survey'[ANSWER]),
FilteredPeople,
'Survey'[ID_QUESTION] = QuestionToAnalyze
)
RETURN
IF(Result > 0, Result / COUNTROWS(FilteredPeople), BLANK())
  • Dpit's avatar
    Dpit
    New Member
    3 years ago

    Sorry for the delay in response, I had to work on another point.

    Thank you very much for your proposal which I tried to adapt without success.

    But your code gave me an idea to modify the data upstream.

    Thank you for your formula that I keep aside for next time, I didn't know we could do so much with dax 🙂