Create a measure with a filter

[jdballard30]

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

[v-jiascu-msft · Accepted solution]

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]
)

Best Regards,
Dale

[HotChilli]

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]

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]

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.

[jdballard30]

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

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