DAX challenge - How to create a formula to calculate a multiple answer results survey

[Anonymous]

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 !

[technolog · Accepted 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
    )
)