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.
Hi Community!
I have a table with survey responses and response date as on the screenshot attached. Also, I have a calendar with the predefined reporting periods like on another screenshot (typically bi-weekly, except the first three weeks of Jan 2020). What is the optimal way to match my responses with the calendar table?
One matter to consider is that I cannot edit responses in m-query for Responses table, bcs this query is uploaded from the database that I cannot access.
Thanks.
Solved! Go to Solution.
Hi @Anonymous ,
First create a column in table "Trigger_quest" to get the date of the column Response_Date,using below dax expression:
Column = DATE(YEAR('Table (2)'[Date]),MONTH('Table (2)'[Date]),DAY('Table (2)'[Date]))
Then create a calculated column as below:
Column 2 = CALCULATE(MAX('Table'[Week]),FILTER('Table','Table (2)'[Column]>='Table'[Start date]&&'Table (2)'[Column]<='Table'[End date]))
Finally you can use week period to create relationships between the 2 tables.
Here is a sample .pbix file you can refer to.
Hi @Anonymous ,
First create a column in table "Trigger_quest" to get the date of the column Response_Date,using below dax expression:
Column = DATE(YEAR('Table (2)'[Date]),MONTH('Table (2)'[Date]),DAY('Table (2)'[Date]))
Then create a calculated column as below:
Column 2 = CALCULATE(MAX('Table'[Week]),FILTER('Table','Table (2)'[Column]>='Table'[Start date]&&'Table (2)'[Column]<='Table'[End date]))
Finally you can use week period to create relationships between the 2 tables.
Here is a sample .pbix file you can refer to.
Hi @v-kelly-msft,
This is super cool, your solution is working! The only thing is that when I sorted my column Period title by Period # there was an issue with the calculated column 2. To overcome this I've created a duplicate column with bi-weekly periods in calendar table and sorted it by Period #.
Also, can you please advise on how do I modidy the syntax to add year to my bi-weekly periods in Trigger_quest table? My survey is going to continue in 2021 onwards, that's why I will modify my calendar table by adding year to period titles (like Weeks 1-3 '20, Weeks 3-6 '20).
Thanks.
Hi @Anonymous ,
For requirement 1:
-Sort Column Period title:
You can go to query editor >add an index column>then sort by index column :
For requirement 2:
You can create a calculated column to achieve it:
column = 'Table'[Week]&" '"&LEFT(YEAR('Table'[Start date]),2)
Finally you will see:
For the related .pbix file,pls click here.
@Anonymous
Not sure if I got it.
But response date seems to have timestamp
Create a new date from it and join with your date table
response date = [response_date].date
Hi @amitchandak,
Dates from Responses table need somehow fit into the bi-weekly periods I have in Calendar table. I mean that responses received on 01 Jan 2020, 08 Jan 2020 and 11 Jan 2020 all belong to the period Week 1-3, etc. And here I cannot get the idea on how to match.
Thanks.
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |