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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors