Results 1 to 2 of 2

Thread: Using ALL to return a table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Using ALL to return a table

    Hello,

    I have a Fact table like this:

    Product Customer Region
    Date Sales

    In the middle of a calculation, I would like to put that table in a variable with the following filter:


    Based on context Based on context Based on context Any date Based on context
    Product Customer Region Date Sales



    ALL seems to be the right function but I only manage to use it in a calculation with formula like this:

    CALCULATE(
    SUM(Fact[Sales] ,
    All(Fact[Date]))



    How could I use it to return the table above?


    Thanks for your help!






  2. #2
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    3,659
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Using ALL to return a table

    You can use SQL to return a portion of your table. You will have to modify the SQL string to meet your requirements. If there are any spaces in your header cells or worksheet name, replace their spaces with underscores in the SQL string. The name of the worksheet that holds the table (in the FROM part) must be followed by a dollar sign.

    Code:
    Option Explicit
    
    Sub CreateKEAsOf(Optional dteData As Date)
    
        Dim sSQLString As String
        Dim aryReturn As Variant
        Dim sDBPath As String
        Dim sConnect As String
        
        'Dim Conn As New ADODB.Connection
        'Dim rs As New ADODB.Recordset
        'Above 2 lines replaced by next 4 to allow late binding
        Dim conn As Object  ' As ADODB.Connection
        Dim rs As Object  ' As ADODB.Recordset
        Set conn = CreateObject("ADODB.Connection")
        Set rs = CreateObject("ADODB.Recordset")
        
        'Your SQL Statement (Table Name=Sheet Name, coded as: [Sheet1$])
        'Don't forget ending space for all-but-last row in multi-line statements
                  
         sSQLString = _
            "SELECT * " & _
            "FROM [WorksheetName$] "
                  
        sDBPath = ThisWorkbook.FullName
        'You can provide the full path of your external file as shown below
        'sDBPath ="C:\InputData.xlsx"
        sConnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & sDBPath & ";HDR=Yes';"
        'If any issue with MSDASQL Provider, Try the Microsoft.Jet.OLEDB:
            'sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath _
            & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
        conn.Open sConnect
        rs.Open sSQLString, conn
        '=>Load the Data into an array
        aryReturn = rs.GetRows
    
    End Sub
    Phil

    - Use CODE tags to keep your code formatted. See: BB Tags
    - How to attach Screenshots
    - Try searching for your answer first, see how
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •