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.
Hi, I am stuck....
I have a query with these records:
Users are asked questions during the day.
One of the questions "Have you taken your study medication TODAY?" is asked at 9:00 in the morning.
-If they answer "No" the same question will be asked again at 12:00
-If the don't answer the question ("notAnswered") the same question will be asked again at 12:00
-If they answer "Yes" nothing happens at 12:00 (but the question will be in the records with "FALSE" in column "needToAnswer"
The next day it starts again at 9:00
The records in the query contain other questions and answers and questions and answers from other users.
The column "Outcome for that day" show the outcome of the 2 questions for that day for that user (this is a fictional column)
I want to be able to calculate the definitive answer every "questionDay" per user. And create a visual of that.
I have attached an example of the query and a example of the visual I want to make.
Thanks a lot in advance, I am totally lost...
govi
Query:
Visual:
Solved! Go to Solution.
@govi
I have created a column to calculate the outcome, let me know how the percentage should b calucated if you need help.
Outcome =
VAR __userid = Table1[userld]
VAR __questionday = Table1[question Day]
VAR __T = CALCULATETABLE( VALUES( Table1[Answer]), Table1[userld] = __userid, Table1[question Day] = __questionday, REMOVEFILTERS() )
VAR __Y = "Yes"
VAR __N = "No"
VAR __NA = "n/a"
VAR __NotA = "not answered"
VAR __RESULT =
SWITCH(
TRUE(),
__Y in __T , __Y,
NOT __Y in __T && __N IN __T , __N,
COUNTROWS(__T) = 1 && __NotA IN __T , __NotA
)
RETURN
__RESULT
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@govi
I have created a column to calculate the outcome, let me know how the percentage should b calucated if you need help.
Outcome =
VAR __userid = Table1[userld]
VAR __questionday = Table1[question Day]
VAR __T = CALCULATETABLE( VALUES( Table1[Answer]), Table1[userld] = __userid, Table1[question Day] = __questionday, REMOVEFILTERS() )
VAR __Y = "Yes"
VAR __N = "No"
VAR __NA = "n/a"
VAR __NotA = "not answered"
VAR __RESULT =
SWITCH(
TRUE(),
__Y in __T , __Y,
NOT __Y in __T && __N IN __T , __N,
COUNTROWS(__T) = 1 && __NotA IN __T , __NotA
)
RETURN
__RESULT
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I forgot a column: both questions have a unique "questionId"
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 |
---|---|
98 | |
96 | |
75 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |