Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
DAX Assistance -- Need Cross Apply for All Dates

Unanswered DAX Assistance -- Need Cross Apply for All Dates

  • Thursday, February 07, 2013 1:09 PM
     
     
    Sign In to Vote
    0

    I'm new to DAX but have been using the basic functions of Power Pivot for a while.   Basically I have a dataset that I'm pulling in from SQL server.   It contains $$s over time from specific snapshots of a data set.  There are 4 attributes that I have slicers hooked to, one of them being the date.

    I have a second data set that is using a linked table to data in a spreadsheet.   This data contains three of the four attributes but is missing the date.   The $$s on this tab are not time dependant so they apply since the beginning of time and go on forever.

    I want to take the second data set and put it on the same graph as the first.   Seeing as how I want to see the change over time for the first set I put the date attribute in the Axis Field.   I want the second data set to be solid straight lines that cover from the beginning of the chart to the end.

    I was thinking if I can cross apply the second data set to all the dates in the first data set, it would give me a data point that would fit my chart.  How do I go about this?   Also when implementing DAX is it only in a new measure?

    Thank you for any help you can provide.

    --You bet I ate it--

     

All Replies

  • Thursday, February 07, 2013 2:51 PM
     
     
    Sign In to Vote
    0

    If you dimensionalize your model, you will get this for free.  Specifically, you can create 4 tables (dimensions) that each contain the distinct list of values for one of the 4 attributes.  And then define relationships from the 2 data tables to the shared tables on the 3 attributes they share. 

    Now, since you don't have a relationship to date from the second data table, when you slice by date, you essentially get a cross join.

    And yes, you'd create a measure on the second data table to plot in the chart.

    Let me know if that helps.

    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

     
  • Thursday, February 07, 2013 3:48 PM
     
     
    Sign In to Vote
    0

    I had the data already split out into dimensions and the relationships were in place. When I threw it out there on the chart I received no data.   I'm thinking right now I had a data problem and I really didn't need to do anything special -like you say to get exactly what I wanted.  I'm going to correct my data sets, re-run and see if my problem disappears.

    --You bet I ate it--

     
  • Thursday, February 07, 2013 6:33 PM
     
     
    Sign In to Vote
    0
    Yes, it was a data issue.   When nothing showed up I thought I would have to force a cross apply.   I guess this goes to show start with the simple and obvious first.    Thanks for the help.

    --You bet I ate it--