Search found 282 matches

by PhilC
Wed Aug 04, 2021 9:14 am
Forum: Power Query
Topic: Extract static data from data models in other Power Pivot workbooks
Replies: 3
Views: 549

Re: Extract static data from data models in other Power Pivot workbooks

Not looking good Andre. It doesn't look like a connection can be made to the data model in Excel. I suppose that does make a bit of sense as it is not one flat dataset. Not ideal, but can you go into each file and push the data you need to be able to review to a table, then use that? Might not work ...
by PhilC
Thu Jul 29, 2021 1:37 pm
Forum: Power BI Desktop/Service
Topic: Visualize Plan vs Actual
Replies: 1
Views: 328

Re: Visualize Plan vs Actual

Hi prima, this was a while ago, so you might have solved it. You need to use multiple connections to a date table and use the date table on your visual. See https://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/ for an example, lots of other examples on the interent. Cheers Phil
by PhilC
Thu Jul 29, 2021 11:32 am
Forum: PowerPivot/DAX
Topic: Calculations
Replies: 1
Views: 361

Re: Calculations

Hi Rob, can you add another table to the pic that has the actual #s expected, so it is clear what all the cells reflect?

Cheers
Phil
by PhilC
Tue Jun 15, 2021 3:02 pm
Forum: Power Query
Topic: Compare to Tables
Replies: 2
Views: 791

Re: Compare to Tables

Regarding having multiple columns, pivot them, so you have Attribute column with the column heading and Value with the value, keeping Key as its own column. Then you can link Table A and Table B on the combined fields Key - Attribute. You would do this three times: 1. an Inner Join, which you then t...
by PhilC
Tue Jun 15, 2021 2:55 pm
Forum: Power Query
Topic: Complex Previous Heading Extraction Power Query
Replies: 1
Views: 861

Re: Complex Previous Heading Extraction Power Query

Used your data pastes into a table, so two columns. Not sure if Code should be used or is relevant, so have not used it. let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Added Custom" = Table.AddColumn(Source, "New Name", each if Text.Contains([Name],&quo...
by PhilC
Tue Jun 15, 2021 2:39 pm
Forum: PowerPivot/DAX
Topic: Calculate sum of a column partly basis daily sum and partly monthly sum
Replies: 1
Views: 715

Re: Calculate sum of a column partly basis daily sum and partly monthly sum

Look into HASONEVALUE() function, combine it with an IF statement. When HASONEVALUE is true, you have a single date, so use the appropriate formula for that, when HASONEVALUE is false, you are showing a Month, so use the appropriate formula for that. Posting your existing formula will help a solutio...
by PhilC
Tue Jun 15, 2021 2:36 pm
Forum: PowerPivot/DAX
Topic: Tool for DAX development in Excel
Replies: 1
Views: 779

Re: Tool for DAX development in Excel

Can you put PowerPivot into Manual Recalculation mode and then only recalc when you want to see updated results? Other options would be to work with a reduced dataset while developing, put a small representative model into duplicate tables and use that, dump representative data into Excel and use th...
by PhilC
Tue Jun 15, 2021 2:32 pm
Forum: PowerPivot/DAX
Topic: PowerPivot doesn't recognize SWITCH syntax
Replies: 2
Views: 426

Re: PowerPivot doesn't recognize SWITCH syntax

Hi george,

You are missing the final, catch all value after [Volumn Share], the [, <else>] portion based on the Function definition.

You need to put another "," and then whatever value is to be returned if none of the previous ones are in [MeasureSelector]

Cheers
by PhilC
Mon Jul 29, 2019 11:58 pm
Forum: PowerPivot/DAX
Topic: Related Tables
Replies: 3
Views: 1336

Re: Related Tables

Hi Anthony, Theory is just that, and it may not always translate into the best solution for any given model. That said, those that have built many models provide best practice suggestions based on what works in many cases. Matt's post here might be a useful start: https://exceleratorbi.com.au/the-op...
by PhilC
Mon Jul 29, 2019 8:26 am
Forum: PowerPivot/DAX
Topic: Cumulative sum of monthly average values
Replies: 1
Views: 805

Re: Cumulative sum of monthly average values

This might give some ideas: https://youtu.be/hH_uiw7f900

Go to advanced search