Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Order Report (DAX Expression Help)

Have a SQL query that pulls order data every Monday.

 

Have the data parameters set as: 12/31/2017 --> 12/31/2022.

 

Is it best practice to have years in seperate tables?  Or is the data fine being pulled all together?  Would I just query past years and severe those connections and just keep 2020 alive? 

 

Having trouble with the following measures: Period Variance %; MTD, QTD, YTD (All data in the same table)

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Hello!! What kind of problems do You have when you use those measures??
    When you work with time intelligence and you have to use measures like MTD or YTD You have to use time table (another way is build your own measures all in the dame table bit this is not efficient).
    Regards
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Having troubles writing the measure with DAX.

       

      Would you need a measure for each of the following: total orders 2018, total orders 2019, total orders 2020?  And then use those measures in your variance measure?

       

      Should I have a different query connection for each year?  Each years has it's own table?

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        The Best aproach to solve this kind of problems, is use just one table with all your data and share the metrics in diferents years (one metric for 2018, another for 2019 and the last for 2020). Finally You can get the variance using the diferents metric that you develop.
        Regards!!
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi.

     

    First piece of advice: Don't take advice from people who are themselves new to Power BI and DAX. They very often promote very bad habits they are not even aware of. The result is not only awkward models they set up but also incorrect DAX they write thinking everything's working. They are then not even able to spot issues. DAX is simple but very dangerous if used without knowledge.

     

    Second, try to learn something about good model design before you embark upon a project. It'll save you countless hours of scratching your head and not being able to figure out why the figures are wrong. Worse, if the model is wrong, no DAX will fix it.

     

    Third, I'll give you some advice about good design.

     

    1. Always create star/snowflake schemas out of your tables.

    2. Never, ever join 2 fact tables directly, only through dimensions.

    3. Fact tables should never be exposed. They must be hidden.

    4. Dimensions should be conformed and connected to your facts.

    5. Slicing is done only through dimensions, never directly on fact tables.

    6. Always create calendars for the relevant date columns in your fact tables and mark them as DATE TABLE in the model. This will enable time-intelligence functions.

    7. Use one-directional filtering 99% of the time. Bi-directional filtering is DANGEROUS and before you decide to use it, know very well the consequences of doing so. Many people think they should have bi-dir filtering on for all relationships. THIS IS SOOOOOOOOOO WRONG. They'll be producing wrong numbers before they know it and will not even be able to spot the issue.

     

    These are some of the golden rules of dimensional modeling with DAX and PBI. If you stick to them, you'll save yourself a lot of grief and head-scratching. Even more, you'll be able to write simple and predictable DAX.

     

    Now, when the model in RIGHT, your measures are easy to write:

     

    // Bear in mind Calendar must be
// a Date Table connected to the
// fact table and marked as
// DATE TABLE in the model. Calendar
// must contain ALL SEQUENTIAL dates 
// covering all the years found in
// the fact table. Anything else...
// and time-intel functions will not
// work correctly.
MTD =
var __oneMonthVisibleOnly =
	HASONEVALUE( 'Calendar'[YearMonth] )
var __mtd =
	CACULATE(
		[Measure],
		DATESMTD( 'Calendar'[Date] )
	)
return
	if( __oneMonthVisibleOnly, __mtd )

     

    The others are similar...

     

    Best

    D