Search found 896 matches

by MattAllington
Tue Dec 12, 2017 6:47 am
Forum: PowerPivot/DAX
Topic: Circular Reference
Replies: 4
Views: 159

Re: Circular Reference

I don't know which table has your formula, and as I said previously (I think) is that it is hard for me to help because I don't understand all the background and the effort for me to come up to speed is high. So my approach to this is to try to guide you to help yourself. The difference between a me...
by MattAllington
Sat Dec 09, 2017 7:12 am
Forum: PowerPivot/DAX
Topic: How to calculate 2 values from the same column
Replies: 4
Views: 98

Re: How to calculate 2 values from the same column

I mean the pivot table, sorry.

Create a new pivot table
Put the 4 columns in your table 2 on rows in the pivot
Change the pivot table settings to be Tabular format and not show sub totals
Add the measure to values
by MattAllington
Fri Dec 08, 2017 5:51 am
Forum: PowerPivot/DAX
Topic: How to calculate 2 values from the same column
Replies: 4
Views: 98

Re: How to calculate 2 values from the same column

This should be standard dax. Load the original table as is. Set up a new table like your table 2. The formula for the measure is

Raw=sum(tablename[act])
Final=Divide(calculate([raw],tablename[nb_na]=“recette”),calculate([raw],tablename[nb_na]=“frequentation”)
by MattAllington
Thu Nov 30, 2017 5:22 pm
Forum: PowerPivot/DAX
Topic: Pivot Table with last date transactions [Solved]
Replies: 1
Views: 89

Re: Pivot Table with last date transactions [Solved]

There are a few ways, but probably the easiest and maybe even most efficient is to add a calculated column to your calendar table something like this. I think this will work IsLastDate = if(Calendar[Date]) = lastdate(TransactionTable[Date]),TRUE(),FALSE()) You can then filter on this column in your ...
by MattAllington
Thu Nov 30, 2017 5:16 pm
Forum: Power Query
Topic: Formatted data Cleaning
Replies: 1
Views: 117

Re: Formatted data Cleaning

How about this as an approach. 1. Duplicate the column 2. Split on the space character 3. add a custom column that checks the length of the 3 columns to make sure they are 3, 4, 4 respectively Custom column = Table.AddColumn(#"Split Column by Delimiter", "Custom", each if Text.Le...
by MattAllington
Thu Nov 30, 2017 12:16 pm
Forum: PowerPivot/DAX
Topic: Circular Reference
Replies: 4
Views: 159

Re: Circular Reference

Well it's a lot to try to take in. But from what I can see, [Anchor 2] is being referenced inside E20-Anchor2 and then you are trying to add it again into a downstream measure. Maybe you can just duplicate the measure Anchor 2 and use the same formula. I don't know if that would work or not. Another...
by MattAllington
Wed Nov 29, 2017 10:02 am
Forum: Power Query
Topic: Which tables/queries are using most space and refresh time
Replies: 3
Views: 123

Re: Which tables/queries are using most space and refresh time

Thanks. Yes it seems there is a new bug in my workbook. MS keeps changing how it works and I have to keep changing the workbook. You can use DAX Studio to extract the table sizes manually. Here is an intro to DAX Studio https://exceleratorbi.com.au/getting-started-dax-studio/ And here is the SQL to ...
by MattAllington
Wed Nov 29, 2017 7:39 am
Forum: PowerPivot/DAX
Topic: Get value for next date based on a condition
Replies: 3
Views: 99

Re: Get value for next date based on a condition

I guess you can do this in Power Query with a simple if statement. Power Query is case sensitive.

Add a custom step

=if [entry] >= #datetime(0,0,0,14,0,0) and [exit] >= #datetime(0,0,0,0,0,0) then "something" else "something else"
by MattAllington
Tue Nov 28, 2017 7:38 am
Forum: Power Query
Topic: Which tables/queries are using most space and refresh time
Replies: 3
Views: 123

Re: Which tables/queries are using most space and refresh time

Hi Ian Is it Power Pivot or Power BI? If Power Pivot, use https://www.sqlbi.com/tools/power-pivot-utilities/ to check the table size. If it is Power BI, you could try my local host workbook https://exceleratorbi.com.au/measure-dependencies-power-bi/ For Queries, you may need to write some VBA to rec...
by MattAllington
Tue Nov 28, 2017 7:34 am
Forum: PowerPivot/DAX
Topic: Updating data srouce with new columns
Replies: 1
Views: 108

Re: Updating data srouce with new columns

Excel 2016 has a bug. You should be able to go into Table Properties in Power Pivot, edit the query and add the 2 columns back with the wizard. In some version of Excel 2016 this option is greyed out and you can't make any changes. But you could still try this. Design\Table Properties

Go to advanced search