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.
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
Solved! Go to Solution.
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())
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 🙂
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())
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
104 | |
79 | |
68 | |
61 |
User | Count |
---|---|
144 | |
104 | |
103 | |
82 | |
70 |