Search found 27 matches

by ozeroth
Mon Jul 20, 2020 10:47 pm
Forum: PowerPivot/DAX
Topic: Time Periods
Replies: 2
Views: 276

Re: Time Periods

Hi Ian, I have attached a sample of how I would set up the data model. 1. Schedule table is expanded so that for each original row there is one row per TimeID (15 minute block). I did this in Power Query. 2. The Schedule table is related to Date & Time dimension tables. 3. Create a measure showi...
by ozeroth
Sun Jul 19, 2020 11:46 am
Forum: Power Query
Topic: Excel Connection String
Replies: 1
Views: 305

Re: Excel Connection String

Hi Ruth, You might have already solved this yourself :) You're right - the first hex-string within the connection string is a unique ID of the dataset on your tenant, and you should just need to change that string to point to a different dataset. To find a dataset's ID, go to the Workspace containin...
by ozeroth
Fri Jun 12, 2020 10:07 pm
Forum: PowerPivot/DAX
Topic: Dependent slicers
Replies: 3
Views: 446

Re: Dependent slicers

Hi there, It sounds like you just need to tweak the slicer settings. For each slicer, right-click the slicer => Slicer Settings => Tick "Hide items with no data". Since it sounds like the slicers all use columns from the same table, they will automatically cross-filter each other. If the s...
by ozeroth
Mon Jan 27, 2020 9:36 pm
Forum: PowerPivot/DAX
Topic: DAX measure to count distinct dates between date ranges over multiple rows [Solved]
Replies: 2
Views: 728

Re: DAX measure to count distinct dates between date ranges over multiple rows [Solved]

Hi there, Below are some ideas (Excel file attached). (Sorry about code formatting - couldn't get the DAX or code tags to work for some reason.) You could write the below measure just using the Trips table you posted, making no use of other tables: Distinct Trip Dates (No Date table):= VAR TripsPlus...
by ozeroth
Tue Oct 30, 2018 10:12 pm
Forum: PowerPivot/DAX
Topic: Modifying the Granularity of SUMX (?)
Replies: 4
Views: 1502

Re: Modifying the Granularity of SUMX (?)

Hi Jonny, I have added a measure in the attached file called Lost Revenue SUMX which I think meets your needs. Have a look and post back if needed. The measure iterates over a table summarized at the appropriate level of granularity, created with the SUMMARIZE function. I used the granularity of Mon...
by ozeroth
Mon Oct 22, 2018 10:01 pm
Forum: Power Query
Topic: select first workbook?
Replies: 2
Views: 938

Re: select first workbook?

Hi there, Yes, you can. In short, the steps are: ⋅ Use Excel.Workbook(...) to open the workbook, which gives you a table of Sheets and Defined Names. ⋅ Filter to Sheets only ⋅ Drill down to the Data column of the first row of the remaining table (corresponding to the fi...
by ozeroth
Sat Oct 20, 2018 7:05 pm
Forum: PowerPivot/DAX
Topic: Modifying the Granularity of SUMX (?)
Replies: 4
Views: 1502

Re: Modifying the Granularity of SUMX (?)

Hi Jonny The short answer to your question is YES, you can use DAX to control the granularity of the table passed to SUMX (or any other iterator function). You can use various DAX functions to produce a table of the required granularity, and then pass this table to SUMX. To give an example, suppose ...
by ozeroth
Sat Jul 07, 2018 10:40 am
Forum: PowerPivot/DAX
Topic: Product Grouping [Solved]
Replies: 5
Views: 2064

Re: Product Grouping [Solved]

That's good news , you're welcome! :)
by ozeroth
Sat Jun 30, 2018 1:58 pm
Forum: PowerPivot/DAX
Topic: Product Grouping [Solved]
Replies: 5
Views: 2064

Re: Product Grouping [Solved]

Hi Kimberly, An idea that sprung to mind when I saw your first post was to create a calculated column in the Customer table that concatenates related values of Product Category. This column will tell you which Product Categories each Customer has in the entire dataset. The simplest way I can think o...
by ozeroth
Sat Jun 23, 2018 10:13 pm
Forum: PowerPivot/DAX
Topic: [Solved] what is the weight of an item in it's category?
Replies: 3
Views: 1608

Re: what is the weight of an item in it's category?

Hi Bernard, I would recommend something like this (using the naming from the Excel file you posted): Tot_Cat1=CALCULATE([Total],ALL( Table1[what] ), VALUES ( Table1[cat1] ) ) This will give you the total for the visible Categories, removing any filters on what, but retaining ...

Go to advanced search

cron