Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello to all DAX wizz ,
I have a DIM table of survey questions and answers ( containing all possible combinations of it )
and it looks like this :
QUE_ANS_ID ( PK )
QUE_ID
QUE_TITLE
ANSWER_ID
ANSWER_VALUE
and a fact table of customers answers that built like this :
QUE_ANS_ID
CUST_ID
QUE_SYS_ID
ANSWER_ID
QUE_TITLE
ANSWER_VALUE
How can I create a filter/ measure that showes a combination of selected answers in the answer value coulmn instead of only one ?
In other words I want to " translate " this SQL query to DAX :
select count (cust_id )
from a_fact_cust_answ
where que_id in ( 1,2)
AND answer_value in ('Male' , 'Yes')
Today , for example ,if I want to see all Male customers that own a car , I get a results of all male and ALL the customers that own a car
I think it can be done by a complex filter but i dont realy know how to format it ( q&a in hirrachy and the count of the ID)
Any help would be much appreciated
Thanks !
Solved! Go to Solution.
To create a measure in DAX that replicates the SQL query to count customers based on multiple answer combinations, you'll need to use a combination of DAX functions to filter and count the relevant rows. Here’s how you can approach this:
Create a Measure for the Count of Customers Matching Multiple Answers:
Here, we will create a measure to count customers who answered "Male" for one question and "Yes" for another question.
CountMatchingCustomers =
VAR QueIDs = {1, 2} -- List of question IDs
VAR AnswerValues = {"Male", "Yes"} -- List of answer values
VAR CustomerAnswers =
SUMMARIZE(
FILTER(
a_fact_cust_answ,
a_fact_cust_answ[QUE_ID] IN QueIDs &&
a_fact_cust_answ[ANSWER_VALUE] IN AnswerValues
),
a_fact_cust_answ[CUST_ID],
a_fact_cust_answ[QUE_ID],
a_fact_cust_answ[ANSWER_VALUE]
)
RETURN
CALCULATE(
COUNTROWS(
SUMMARIZE(
CustomerAnswers,
a_fact_cust_answ[CUST_ID]
)
),
FILTER(
CustomerAnswers,
COUNTROWS(
FILTER(
CustomerAnswers,
a_fact_cust_answ[CUST_ID] = EARLIER(a_fact_cust_answ[CUST_ID])
)
) = 2 -- Number of conditions to match
)
)
To create a measure in DAX that replicates the SQL query to count customers based on multiple answer combinations, you'll need to use a combination of DAX functions to filter and count the relevant rows. Here’s how you can approach this:
Create a Measure for the Count of Customers Matching Multiple Answers:
Here, we will create a measure to count customers who answered "Male" for one question and "Yes" for another question.
CountMatchingCustomers =
VAR QueIDs = {1, 2} -- List of question IDs
VAR AnswerValues = {"Male", "Yes"} -- List of answer values
VAR CustomerAnswers =
SUMMARIZE(
FILTER(
a_fact_cust_answ,
a_fact_cust_answ[QUE_ID] IN QueIDs &&
a_fact_cust_answ[ANSWER_VALUE] IN AnswerValues
),
a_fact_cust_answ[CUST_ID],
a_fact_cust_answ[QUE_ID],
a_fact_cust_answ[ANSWER_VALUE]
)
RETURN
CALCULATE(
COUNTROWS(
SUMMARIZE(
CustomerAnswers,
a_fact_cust_answ[CUST_ID]
)
),
FILTER(
CustomerAnswers,
COUNTROWS(
FILTER(
CustomerAnswers,
a_fact_cust_answ[CUST_ID] = EARLIER(a_fact_cust_answ[CUST_ID])
)
) = 2 -- Number of conditions to match
)
)
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |