Forum Discussion

jdballard30's avatar
jdballard30
Helper II
7 years ago
Solved

Create a measure with a filter

I have the following data; it's a table that is denormalized and is the result of the join of App and Transaction.

 

AppID     TransactionID     DaysBetweenEvents

DEF       123               3

DEF       880               3

LMN       228               6

LMN       318               6

XYZ       414               9

 

The column "DaysBetweenEvents" will always be the same for any row that has the same AppID. What I'm trying to do is get the average days between events, but based on the DaysBetweenEvents for each application, without regard for the number of transactions. So, for the data above, the average would be (3  + 6 +  9) / 3, which is 6 (there are three unique applications, app DEF has 3 days between, LMN has 6 days between, and XYZ has 9.) The problem is that Power BI is calculating it as 5.4, because it's adding all the rows and dividing by 5.

 

How can I create a measure to calculate this? Do I need to change my model or is there DAX syntax I can use?

 

I've include a link to a .pbix file (Average.pbix) that has the above data (and a little more) so you can see what I'm trying to do. Perhaps that'll help. https://1drv.ms/f/s!AlWcOa_mDYfDq94uNPUcUD3JxLJ0VQ

 

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi jdballard30,

     

    You also can use a measure instead. There could be two approaches.

    Measure =
AVERAGEX (
    DISTINCT (
        SELECTCOLUMNS (
            ProgramTransactions,
            "Col1", [AppId],
            "Col2", [DaysBetweenEvents]
        )
    ),
    [Col2]
)

    Measure 2 =
AVERAGEX (
    SUMMARIZE (
        'ProgramTransactions',
        'ProgramTransactions'[AppId],
        'ProgramTransactions'[DaysBetweenEvents]
    ),
    [DaysBetweenEvents]
)

    Create-a-measure-with-a-filter

     

    Best Regards,
    Dale

5 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    7 years ago

    I think you need to get the distinct appid, daysbetweenevents rows of your table . Something like 

    NewTable = DISTINCT(SELECTCOLUMNS(YourTable, "ID", YourTable[AppId], "Days",YourTable[AverageDays]))

    and then create a measure that takes the average of the Days column

    NewMeasure = AVERAGE(NewTable[Days])

    and stick that measure on a card

     

    • jdballard30's avatar
      jdballard30
      Helper II
      7 years ago

      I'm a bit confused, and it's me, not you. :smileyhappy:

       

      Would the new table be created using the query editor or DAX?

    • jdballard30's avatar
      jdballard30
      Helper II
      7 years ago

      So I figured it out.

       

      I used the "New Table" option on the Modeling menu to add the table. So that obviously creates a table, and I see how the distinct columns works. In my linked .pbix, I got the result I wanted.

       

      I guess my question is: I could do this in my queries when everything is loaded. Is there a reason to do it in the front-end as opposed to the back-end or vice versa?

      • HotChilli's avatar
        HotChilli
        Community Champion
        7 years ago

        It's entirely up to you.   You can do the same algorithm in power query editor or advanced query editor using M.

        If you have better skills in DAX, then use that.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi jdballard30,

     

    You also can use a measure instead. There could be two approaches.

    Measure =
AVERAGEX (
    DISTINCT (
        SELECTCOLUMNS (
            ProgramTransactions,
            "Col1", [AppId],
            "Col2", [DaysBetweenEvents]
        )
    ),
    [Col2]
)

    Measure 2 =
AVERAGEX (
    SUMMARIZE (
        'ProgramTransactions',
        'ProgramTransactions'[AppId],
        'ProgramTransactions'[DaysBetweenEvents]
    ),
    [DaysBetweenEvents]
)

    Create-a-measure-with-a-filter

     

    Best Regards,
    Dale