Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Basic Newbie Question

  • 1.  Basic Newbie Question

    Bronze Contributor
    Posted Aug 19, 2019 02:59 PM
    Just beginning to learn Power BI and am running into some basic problems.

    I have a data set that includes:

    TransactionDate as a timestamp (which correlates to the individual customer receipts)
    Location
    Qty
    Sale

    I created a calendar with all the various date parts covering the period from MIN([TransactionDate] to MAX([TransactionDate]) and joined the Date field with TransactionDate.

    I created a measure of Total Sale = [qty] * [sale]  :: SUMX('sales', [qty]*[sale])

    If I create a table view in Modeling and add TransactionDate, Location and Total Sales, the aggregation occurs at the timestamp level (even if I format the date as mm/dd/yyyy).  If I use the sDate field from my Date table (short date) I get the same value for every date (although the aggregation seems to work at the date (not timestamp) level.

    Clearly a newbie screw-up but I just can't seem to figure out what I'm doing wrong.

    G

    ------------------------------
    Gerry P
    ------------------------------
    Conference-PBI_200x200


  • 2.  RE: Basic Newbie Question

    Posted Aug 20, 2019 04:16 AM
    Hi Gerry,

    I would recommend creating a calendar from scratch instead of basing it on another date column. This will ensure that you dont have any dategaps which will be an issue if you later on try to implement Time Intelligence features.

    See: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/Create-a-Calendar-with-DAX-in-Power-BI/td-p/176357

    Seeing as you have timestamps in your main dataset, try to work with the timestamp column to create a new column labeled as DateKey, a DateKey looks like this "20190820" in integer form.

    The relation between your calendar and your main dataset will be the DateKey column. You can also work to divide the timestamp into multiple columns such as "Date" YYYY-MM-DD and "Time" HH:mm:ss"

    Separating it into specific columns will enable you to slice the data on both dates and time.

    If you do all this and then slice the data using a slicer with only dates you should not get the result on timestamp level. This however also depends on if you are displaying the data in a table where a timestamp is present. If so, remove the timestamp column from the visualization object.

    I am not sure if this is helpful or not but these are my initial thoughts without having screenshots or example data to laborate with.

    ------------------------------
    paul berg
    Business Intellligence Developer
    +46 768501803
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: Basic Newbie Question

    Bronze Contributor
    Posted Aug 20, 2019 09:43 AM
    Hey Paul...  that was my first instinct.  But when I attempt to add a column on my fact table with just the mm/dd/yyyy date, using 
    sDate = value(format([My Date Field],"mm/dd/yyyy")) Power BI tells me that FORMAT is not allowed in a calculated column. What am I doing wrong?  I swear I've seen this solution repeatedly online.  But that, I'm sure, is the issue.  I need the date and time separated but need to do it without FORMAT.


    ------------------------------
    Gerry Price
    ------------------------------

    Conference-PBI_200x200


  • 4.  RE: Basic Newbie Question

    Top Contributor
    Posted Aug 20, 2019 04:18 AM
    Hi @Gerry Price,

    First of all, welcome to the community!

    Please don't hesitate to post any question, no matter how stupid or naive you think it is. We all started somewhere and we all have posted questions we thought were simple for someone else, but that is how we learned.

    Now coming to your query.

    Do you want the aggregation to occur at the time stamp level or that date level?

    There is nothing wrong with the DAX, you will just need to understand how it works.

    Your calendar table generally contains only dates and if you want the time dimension, you can create another table for time.

    I could share a video for how to work with time, if you want.

    Meanwhile, please upload a sample file(or relevant screenshots) for the community, it helps us understand your problem better.

    Again, welcome to the community!

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Conference-PBI_200x200


  • 5.  RE: Basic Newbie Question

    Bronze Contributor
    Posted Aug 20, 2019 12:22 PM
    So...  I believe I've solved my problem.  Most of the suggestions I found suggested creating a column using FORMAT (which Power BI does not allow).  So, what I did was:

    Create a new column in my fact table as a DateKey using:  YEAR([date]) & MONTH([date]) & DAY([date])
    Set the column type as Whole Number
    Add a similar field in my Date Table and configure as Whole Number
    Link on the Date Key

    Seems to work.

    ------------------------------
    Gerry Price
    ------------------------------

    Conference-PBI_200x200


  • 6.  RE: Basic Newbie Question

    Top Contributor
    Posted Aug 21, 2019 04:25 AM
    Hi @Gerry Price​,

    This is in response to your inbox message.

    FORMAT() is a DAX function.

    So, if you are trying to use that in the query editor it will not work.

    A thing that you can do to get the date portion, is you can split the existing column by delimiter or by number of characters. Then you can create a relationship between the date columns itself.

    Ideally, the date key is created only in the calendar table for sorting purposes.
    Creating a date key column in your fact table will unnecessarily bloat your data model in the long run, as you will have multiple dates repeated in your fact table and hence multiple date keys. This will affect report performance (when slicing and dicing) and refresh times.

    So I would suggest that you keep the date key restricted to the calendar table itself and create a relationship between the Date columns of your fact and dim tables only.

    If you need any further assistance, please upload some sample file.

    Hope this makes sense.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Conference-PBI_200x200