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 DE...
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

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.