VISUALIZING & COMBINING MULTIPLE ANSWERS SURVEY

[Anonymous]

Hello to all DAX experts

need your help ASAP, 

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 !

[Anonymous]

Thank you @Icey 

[Icey]

Hi @Anonymous ,

Have you tried Slicer? If it isn't what you want, please give me some data sample.

Best Regards,

Icey

[Anonymous]

@Nathaniel_C  I'll be specific ...  I have a survey 

CUST_ID	QUE_TITLE	ANSWER_VALUE
2318066	Gender	Male
2318066	Do you live in New-York City ?	Yes
2318066	Select the best description of your daily activity level.	Fully active
2318546	Gender	Male
2318546	Do you live in New-York City ?	Yes
2318546	Select the best description of your daily activity level.	Some  Active
2330326	Gender	Female
2330326	Do you live in New-York City ?	Yes
2330326	Select the best description of your daily activity level.	Some  Active
2337367	Gender	Male
2337367	Do you live in New-York City ?	Preffer not to answer
2337367	Select the best description of your daily activity level.	Some  Active
2389586	Gender	Female
2389586	Do you live in New-York City ?	Yes
2389586	Select the best description of your daily activity level.	Some  Active
2399889	Gender	Male
2399889	Do you live in New-York City ?	Yes
2399889	Select the best description of your daily activity level.	Fully active
2421779	Gender	Female
2421779	Do you live in New-York City ?	Yes
2421779	Select the best description of your daily activity level.	Some  Active

How can I create a filter / Slicer  that showes a combination of possible results ? 

for example , Customers that are male AND live in NY AND fully active

@Icey A regular slicer on the answer value  showes my the results for Male OR  live in NY  ( could be female.. )  OR  fully active

Do you have any ideas ?...

Thanks !

[Icey]

Hi @Anonymous ,

I suggest you to use "Pivot column" in Power Query Editor. PBIX file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you @Icey  

that works perfect ,

HOWEVER  here is where things gets complicated for me :

what if the customer have a multiple option selection in the survey like this example :

CUST_ID	QUE_TITLE	ANSWER_VALUE
2318066	Gender	Male
2318066	Do you live in New-York City ?	Yes
2318066	Select the best description of your daily activity level.	Fully active
2318546	Gender	Male
2318546	Do you live in New-York City ?	Yes
2318546	Select the best description of your daily activity level.	Some  Active , Not sure
2330326	Gender	Female
2330326	Do you live in New-York City ?	Yes
2330326	Select the best description of your daily activity level.	Some  Active  , Not sure
2337367	Gender	Male
2337367	Do you live in New-York City ?	Preffer not to answer , Maybe
2337367	Select the best description of your daily activity level.	Some  Active  , Not sure
2389586	Gender	Female
2389586	Do you live in New-York City ?	Yes
2389586	Select the best description of your daily activity level.	Some  Active  , Not sure
2399889	Gender	Male
2399889	Do you live in New-York City ?	Yes
2399889	Select the best description of your daily activity level.	Fully active
2421779	Gender	Female
2421779	Do you live in New-York City ?	Yes
2421779	Select the best description of your daily activity level.	Some  Active  , Not sure

I tried to separate the column by delemeter but after the separation i get a difrent name in the header

any ideas how to resolve this ?

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

PBIX file attached.

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nathaniel_C]

Hi @Anonymous 
Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490