cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TzachiGolan Frequent Visitor
Frequent Visitor

VISUALIZING & COMBINING MULTIPLE ANSWERS SURVEY

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: VISUALIZING & COMBINING MULTIPLE ANSWERS SURVEY

Hi @TzachiGolan ,

Try this:

gender follow.PNG

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.

View solution in original post

7 REPLIES 7
Nathaniel_C Super Contributor
Super Contributor

Re: VISUALIZING & COMBINING MULTIPLE ANSWERS SURVEY

Community Support Team
Community Support Team

Re: VISUALIZING & COMBINING MULTIPLE ANSWERS SURVEY

Hi @TzachiGolan ,

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

 

Best Regards,

Icey

TzachiGolan Frequent Visitor
Frequent Visitor

Re: VISUALIZING & COMBINING MULTIPLE ANSWERS SURVEY

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

 

CUST_IDQUE_TITLEANSWER_VALUE
2318066GenderMale
2318066Do you live in New-York City ?Yes
2318066Select the best description of your daily activity level.Fully active
2318546GenderMale
2318546Do you live in New-York City ?Yes
2318546Select the best description of your daily activity level.Some  Active
2330326GenderFemale
2330326Do you live in New-York City ?Yes
2330326Select the best description of your daily activity level.Some  Active
2337367GenderMale
2337367Do you live in New-York City ?Preffer not to answer
2337367Select the best description of your daily activity level.Some  Active
2389586GenderFemale
2389586Do you live in New-York City ?Yes
2389586Select the best description of your daily activity level.Some  Active
2399889GenderMale
2399889Do you live in New-York City ?Yes
2399889Select the best description of your daily activity level.Fully active
2421779GenderFemale
2421779Do you live in New-York City ?Yes
2421779Select 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 !

 

 

 

Community Support Team
Community Support Team

Re: VISUALIZING & COMBINING MULTIPLE ANSWERS SURVEY

Hi @TzachiGolan ,

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

gender1.PNGgender2.PNGgender3.PNG

 

Best Regards,

Icey

 

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

Highlighted
TzachiGolan Frequent Visitor
Frequent Visitor

Re: VISUALIZING & COMBINING MULTIPLE ANSWERS SURVEY

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_IDQUE_TITLEANSWER_VALUE
2318066GenderMale
2318066Do you live in New-York City ?Yes
2318066Select the best description of your daily activity level.Fully active
2318546GenderMale
2318546Do you live in New-York City ?Yes
2318546Select the best description of your daily activity level.Some  Active , Not sure
2330326GenderFemale
2330326Do you live in New-York City ?Yes
2330326Select the best description of your daily activity level.Some  Active  , Not sure
2337367GenderMale
2337367Do you live in New-York City ?Preffer not to answer , Maybe 
2337367Select the best description of your daily activity level.Some  Active  , Not sure
2389586GenderFemale
2389586Do you live in New-York City ?Yes
2389586Select the best description of your daily activity level.Some  Active  , Not sure
2399889GenderMale
2399889Do you live in New-York City ?Yes
2399889Select the best description of your daily activity level.Fully active
2421779GenderFemale
2421779Do you live in New-York City ?Yes
2421779Select 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 ?

Community Support Team
Community Support Team

Re: VISUALIZING & COMBINING MULTIPLE ANSWERS SURVEY

Hi @TzachiGolan ,

Try this:

gender follow.PNG

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.

View solution in original post

TzachiGolan Frequent Visitor
Frequent Visitor

Re: VISUALIZING & COMBINING MULTIPLE ANSWERS SURVEY

Thank you @Icey 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)