Search found 863 matches

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: 43

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: 100

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
by MattAllington
Thu Sep 14, 2017 7:42 am
Forum: Power Query
Topic: Input data in a table and then refresh in another
Replies: 6
Views: 100

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

If you want to keep 2 tables, you can start with the current price table, then in Power Query perform a merge with the second table using material number (left outer join). Expand the new table column to extract the new price, then write a custom column something like this If [newprice] = null then ...
by MattAllington
Wed Sep 13, 2017 8:28 am
Forum: Power Query
Topic: Input data in a table and then refresh in another
Replies: 6
Views: 100

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

Power Query can process data, but it is generally not ideal for creating data. As far as I can tell from your example, you have 2 tables. One is "current price" (you call it previous price) and one is a new price. These 2 sets of data are in 2 tables. What happens when the new price become...
by MattAllington
Sun Sep 10, 2017 8:23 am
Forum: PowerPivot/DAX
Topic: Modeling Help
Replies: 1
Views: 78

Re: Modeling Help

Regarding 1) you can't do this in Excel Regarding 2) I am not really sure what you are asking here. I suggest you have a look at my article here as it may help you understand what is happening and what you need to do to make it work the way you want. https://exceleratorbi.com.au/many-many-relationsh...
by MattAllington
Sun Sep 10, 2017 8:17 am
Forum: PowerPivot/DAX
Topic: Creating a Flag for Mismatches
Replies: 1
Views: 79

Re: Creating a Flag for Mismatches

I've had a quick look at the data and can't really understand it. As a general comment however, I recommend using Power Query to cleanse data rather than Power Pivot. Power Pivot is a reporting engine and is not really designed to fix problems in the data. So if some of the data is wrong, I would pe...
by MattAllington
Sun Sep 10, 2017 6:45 am
Forum: PowerPivot/DAX
Topic: new measure not added to pivot table automatically
Replies: 2
Views: 84

Re: new measure not added to pivot table automatically

I guess you are using Excel 2016. The Dev team is trying to fix power pivot and in the process they changed this behaviour (deliberately it seems). I have spokeen to the program manager and he said they are going to change this back to the way it was before (auto add to the pivot). You will just nee...
by MattAllington
Sun Sep 03, 2017 6:15 am
Forum: PowerPivot/DAX
Topic: Iterating through Subsets to Find Previous Value
Replies: 8
Views: 190

Re: Iterating through Subsets to Find Previous Value

Great. Glad it looks right. I wrote the blog yesterday. The article is about how I wrote the formula, not about the formula per se, so it should help you a lot. Your DAX is already good, you just need some tips on how to approach stuff like this.
by MattAllington
Sat Sep 02, 2017 10:47 am
Forum: Testing
Topic: test dax formatter
Replies: 0
Views: 93

test dax formatter

Long DAX = CALCULATE ( MAX ( Changes[new_value] ), FILTER ( Changes, Changes[member_id] = EARLIER ( Changes[member_id] ) ), FILTER ( Changes, Changes[change_type] = "Type" ), FILTER ( changes, Changes[start_date] = CALCULATE ( MAX ( Changes[s...
by MattAllington
Sat Sep 02, 2017 10:45 am
Forum: PowerPivot/DAX
Topic: Iterating through Subsets to Find Previous Value
Replies: 8
Views: 190

Re: Iterating through Subsets to Find Previous Value

How about this? If it is right, i will write a blog post = CALCULATE ( MAX ( Changes[new_value] ), FILTER ( Changes, Changes[member_id] = EARLIER ( Changes[member_id] ) ), FILTER ( Changes, Changes[change_type] = "Type" ), FILTER ( changes, Changes[s...

Go to advanced search