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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. 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.



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.