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.
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 !
Solved! Go to 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.
Hi @Anonymous ,
Have you tried Slicer? If it isn't what you want, please give me some data sample.
Best Regards,
Icey
@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 !
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.
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 ?
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.
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
Proud to be a Super User!
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |