Search found 871 matches

by MattAllington
Fri Oct 13, 2017 7:19 am
Forum: PowerPivot/DAX
Topic: Combining Demand table with date table
Replies: 2
Views: 59

Re: Combining Demand table with date table

Yes, Power Pivot is a great tool for this The first thing you should do is unpivot your first table. You need a table shape like this Part No, Order No, Qty abc, 123,10 def, 123,3 etc This will be your data table Load both of your tables to Power Pivot. The second table is your lookup table. Join th...
by MattAllington
Tue Oct 10, 2017 5:08 pm
Forum: PowerPivot/DAX
Topic: Finding Value of "add a product" Marketing Campaign
Replies: 1
Views: 57

Re: Finding Value of "add a product" Marketing Campaign

From what I can see, your measure returns the count of new sales events (what ever a row is in your table) cumulative since the promotion started (thanks to your slicer filter). Assuming that is correct, why not just replace countrows(table) with sum(table[salesvaluecolumn])
by MattAllington
Tue Oct 10, 2017 2:11 pm
Forum: PowerPivot/DAX
Topic: Hello and Quick Question
Replies: 1
Views: 38

Re: Hello and Quick Question

It can definitely do what you want. Instead of vlookup, you load the lookup tables and then create relationships. Instead of if statements, you can use Power Query on load to improve the categorisation of the data.
by MattAllington
Thu Oct 05, 2017 10:08 am
Forum: PowerPivot/DAX
Topic: GroupBy - create new table in Excel 2016 [Solved]
Replies: 2
Views: 84

Re: GroupBy - create new table in Excel 2016 [Solved]

read towards the end of this article I wrote https://exceleratorbi.com.au/getting-st ... ax-studio/

"Move your query to Excel".

From there, you can add the table back to the data model using "Add to Data Model"
by MattAllington
Wed Sep 27, 2017 10:42 am
Forum: Testing
Topic: Images
Replies: 2
Views: 183

Re: Images

do you mean in this forum?

You can add an attachment
attachment.png
attachment.png (169.44 KiB) Viewed 181 times


after adding it, you can place it in line
place in line.png
place in line.png (231.73 KiB) Viewed 181 times
by MattAllington
Tue Sep 26, 2017 7:55 am
Forum: PowerPivot/DAX
Topic: Previous row / overcoming circular reference
Replies: 1
Views: 93

Re: Previous row / overcoming circular reference

Maybe you can do it in Power Query to make it easier. Check out my video here https://www.youtube.com/watch?v=xN2IRXQ2CvI
by MattAllington
Mon Sep 25, 2017 8:02 am
Forum: PowerPivot/DAX
Topic: Create Relationship When There Are More Than 1 Million Unique Items
Replies: 1
Views: 53

Re: Create Relationship When There Are More Than 1 Million Unique Items

Are you trying to create linked tables from Excel? If so, this is not the way to do it. You should load data directly into Power Pivot from a csv or other source. I have workbooks with 85million rows in a single table. Also you should not load data from different months in different tables, you shou...
by MattAllington
Fri Sep 22, 2017 8:14 am
Forum: PowerPivot/DAX
Topic: Take the selected item in a slicer
Replies: 3
Views: 122

Re: Take the selected item in a slicer

In Power BI there is a new function SELECTEDVALUE. this is just syntax sugar for IF(HASONEVALUE(table[column]),VALUES(table[column]))

Both will return the selected value in a slicer on the column, or a blank in the case that there are multiple values selected.
by MattAllington
Fri Sep 15, 2017 7:59 am
Forum: PowerPivot/DAX
Topic: Where do I put ALL when clearing a Related Table Filter
Replies: 2
Views: 98

Re: Where do I put ALL when clearing a Related Table Filter

Well calculate can have as many filters as you like. =calculate(<measure exp>, filter1, filter2)

You are using filter1 only, so why not try all(yourtable) for filter2
by MattAllington
Thu Sep 14, 2017 7:08 pm
Forum: Power Query
Topic: Input data in a table and then refresh in another
Replies: 6
Views: 257

Re: Input data in a table and then refresh in another

Well the way I would do it is still the way I originally mentioned. Create a table that contains all the details and use time stamps to show which are active. Effort spent up front redesigning the data structure is paid back via simplified reporting

Go to advanced search