Відео

I think you're right - it should be the same as in this video. Trick is just to pick which table you want to start with, then the other measure will be simple. For example, if you take the Facttable = Prices, ManufacturingTime = Quantity, DimMaterial = Articles, and dimProcessCode = DimProduct (a new table you'll want to create) Here's the measure: Prep Time Many to Many = VAR _ProcessCode = VALUES(dimProcessCode[ProcessCode]) RETURN SUMX(Facttable, CALCULATE( SUMX( FILTER(ManufacturingTime , ManufacturingTime[ProcessCode] IN _ProcessCode ) , ManufacturingTime[PrepTime] ) ) ) You should also use a DimDate table if you don't have one already excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html Then just multiply by SUM(Price) or AVERAGE(Price) or MAX(Price) and use the DimProduct and DimDate in your visuals. Hope that helps?

​@@excelwithallison Thank you soo much, it is finally working! :-) I just couldn't find a solution for this problem... finally! I already had the dim tables that you mentioned. I just wanted to keep the context as simple as possible for my question. I was able to edit the majority of my other measures refering to this cost measure now.👍 But in two cases I still have some problems with editing this measure for other calculations. I do get how your measure is working but its kinda hard for me to edit it... 1. Problem: How do I calculate average, min, max costs per date and per product with this measure?😅 2. Problem: I have another table in which the articles are catogoriesed into groups. So its basically another dimtable for articles (or refering to your vid - a dimtable for the Filtertable). And articles then has an extra referring coloumn to the ne group dim table . How do I calculate the costs per group by also filtering date and product again?

Okay i found a solution for the first problem. So only the second one😄

I also solved the second problem. I was able to solve both problems by using SUMMARIZE I can just tell you again, this video helped me out very well. Also thank you so much for replying even tho this video is already a year old. Keep up the good work!👍

@ScharaN so glad you found it helpful, even a year on. There's also AVERAGEX, MAXX, MINX that might be helpful too if you didn't want to use SUMMARIZE but if it's working that's fantastic!

So glad you liked it - thanks for the feedback and you're very welcome.

Great question - I really struggled with that the first time too. Here's a video showing how I did it: ua-cam.com/video/4ruZ4Xf4_fc/v-deo.html

Hi~ Glad you learned a lot from the video. I don't have any resources specific to survey forms that change; it is definitely a challenging data model to work with! It depends how you want to treat the change in questions - is it still valid to compare the data once the question has changed? If so you may want to research 'slowly changing dimensions' and see if that helps at all.

You're very welcome

So glad you found it useful. Yes the premium licensing for the AI text analytics is a bit of a road block for many. I have written a blog post with screenshots of the process but I'll add it to my list of future post ideas and see if I can't do a decent video demo. In the meantime here's the link to the blog post: excelwithallison.blogspot.com/2021/06/text-analytics-in-power-bi.html

It's all about getting the format right in Power Query. What is the source and what is the destination? If your multiple choice answers are one node, what goes in the next node?

Can you provide a bit more info around your source data?

You're very welcome!

This could be done using a matrix and a bit of DAX magic to get the T/F. Otherwise just put the ID in rows, the app in columns, and count the ID from the apps used table in values.

You're welcome!

Hi Jessica~ As long as you haven't changed the questions from the previous survey results, you can do benchmarking or even trend analysis. In this case a Date Dimension table will be necessary to help filter the different survey collection periods. You may wish to add a column to your Date Dimension table that specifies which date ranges apply to each distribution of survey results, or just group by month, year, etc. Here's my post on how you can create a Date Dimension table: excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

@@excelwithallison Thank you Allison for your help!

Hi! Sorry for the late reply. Using the web path ensures that Power BI can access the file from PowerBI.com. In that way, we can auto refresh the data using our Microsoft login credentials.

Here's a link to the DimDate table I use for most of my training videos: excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

Hi Michael~ This is possible if you have a matrix or data table with a list of all the possible responses. I'm doing a talk on this next month at the Power BI Summit: globalpowerbisummit.com/

You're very welcome, let us know what exciting things you do with it and how you get on!

Glad you enjoyed it Amanda! Here's a link to the report and survey data: excelwithallisonmvp.sharepoint.com/:f:/s/ExcelwithAllison/EtBwTVl8p3JHgbMFK2NzgmgBcC8u2Fg2TXhIlEblM8HiRA?e=pChiVJ And here's the link to fill in the form with your own results: forms.office.com/Pages/ResponsePage.aspx?id=y2WOCy7cTESo4BgvKn-HXI8xPYz2TbdKoCHooCjExWFURUQyN08zOVBQWExBQ05VVTBUOFdSMzhDNyQlQCN0PWcu I update the dataset daily so you can see your results in the report the following day. I will also add this info to the video description.

Glad you liked it

Hi Carine~ We didn't really use the DimQuestions table in this particular survey example since we asked so many different types of questions. In some scenarios or datasets though it can be really helpful to help filter your data by Question. This would be helpful if you unpivot the raw data so that you have a column for 'Question' and a column for 'Answer' (similar to what we did in the video with the Likert Responses). It works really well with audits and inspections where each question is a different requirement and you want to be able to analyze them all together. Hope that helps? Allison