Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

DAX to bring only match results from two tables

Hi,

 

I hope this question will be very straightforward for someone.Will really appreciate for the response.

 

For example I have a table called SurveyResults where Q1, Q2 and Q3 has theme saved with score (0-9) in score column

RefDateQ1Q2Q3Score
105/04/2020Happy  3
205/04/2020Sad Unhappy5
306/04/2020 HappyExcited8
407/04/2020Excited  2
506/04/2020  Sad5
608/04/2020Happy  8

 

Date column contains 

DateIdDate
101/04/2020
202/04/2020
303/04/2020
404/04/2020
505/04/2020
606/04/2020
707/04/2020
808/04/2020
909/04/2020
1010/04/2020

 

I wanted to show Q1, Q2 and Q3 appended in one column rather than in three columns so I created reference table in Power Query to append three tables and then bring all three tables into one table called SurveyResultDerived as below. (Please suggest if there is a better way to dealing this). In this table Survey Q is all values from Q1, Q2 and Q3 and Question No is additional column to hold which question no it belongs to. 

RefDateSurvey QScoreQuestion No
105/04/2020Happy3Q1
205/04/2020Sad5Q1
306/04/2020 8Q1
407/04/2020Excited2Q1
506/04/2020 5Q1
608/04/2020Happy8Q1
105/04/2020 3Q2
205/04/2020Unhappy5Q2
306/04/2020Excited8Q2
407/04/2020 2Q2
506/04/2020Sad5Q2
608/04/2020 8Q2
105/04/2020 3Q3
205/04/2020Unhappy5Q3
306/04/2020Excited8Q3
407/04/2020 2Q3
506/04/2020Sad5Q3
608/04/2020 8Q3

 

I was able to show wordcloud using SurveyResultDerived table as it brings all combintion from SurveyResult table and then I got total responses from SurveyResult. 

 

As charts on dashbaord are coming from two tables so selecting one chart does not effect on other chart. How this can be handled? I created a date table in between but if I add a slicer from date table it will bring all dates combinaton from date table whereas i only want matching one.

 

Kindly suggest how this can be achieved?

 

Many thanks

 

2 REPLIES 2
Anonymous
Not applicable

Seems there's too little data to be able to give a definite answer to your question.

Best
D
Greg_Deckler
Super User
Super User

Not sure I am following everything going on here. 

 

First, for your first table, you could have just unpivoted your Q columns versus what it sounds like you did. Just select the three columns, right-click in Query Editor and choose Unpivot.

 

Second, it sounds like you are missing a relationship between your two tables.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors