Dublin Power BI User Group

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

Dax Query

  • 1.  Dax Query

    Posted Apr 25, 2019 10:05 AM
      |   view attached
    Our customers log in with different channels, browser, mobile App and tablet app. Some will use only one channel and others will use multiple. I'm trying (and failing!) to write a measure that allows me know the numbers of customers (users) that have logged in across the various combinations of channels but have the flexibility to filter by a date slicer.

    The source data (CSV's per day per channel) is read in from network folders and appended to one table in PowerQuery (called "Mobile"). For each login a record is written to the file, so the filename gives me the file date and the channel with the data in the file giving the User ID's

    I used values to represent the channels so, 1 = tablet app, 10 = Browser and 100 = Mobile app. The idea being that after grouping by User ID for the filtered date range I could then add the channel values. So for example, if a User ID has a value of 101 I'd know that User ID logged in with both the tablet app and the mobile app but not the browser in the filtered date range.

    The attached pibx file is my failed attempt. On the canvas Table 1 is from the source data prior to any grouping. Table 2 is what I want for the User ID's - but doesn't work with the date slicer, and the row context is lost on the count of logins

    While I made up the sample data for this the real scenario has many multiples the numbers of records.

    Any assistance is much appreciated.

    ------------------------------
    Paul O Sullivan
    Dublin
    ------------------------------

    Attachment(s)

    pbix
    Channels Test data.pbix   135K 1 version


  • 2.  RE: Dax Query

    Posted Apr 25, 2019 10:27 AM

    Hi Paul,

     

    Theres a few ways to do this. Often we use a bitmap for this sort of stuff (1,2,4,8,16), but 10 x will do the same thing. (1,10, 100)

     

    At the end you need two tables (or 3 if date is a dimension and not degenerate)

     

    Usage (summary of Mobile)

    User

    ChannelKey (Hidden)

    Date

    Bill

    101

    01/01/2019

    Ben

    111

    02/01/2019

     

    Channels (reference Matrix table)

    ChanelKey

    Channels

    Channel Usage

    TabletFlag

    BrowserFlag

    Mobile Flag

    1

    Tablet

    1

    1

     

     

    10

    Browser

    1

     

    1

     

    100

    Mobile

    1

     

     

    1

    101

    Tablet, Mobile

    2

    1

     

    1

    110

    Etc

    2

     

    1

    1

    11

    Etc

    2

    1

     

     

    111

    Etc

    3

    1

    1

    1

    etc

     

    You can PowerQuery to "group" your source table using the group by function (as opposed to calculated tables which are way less efficient)

    -          First to Group by User, Date, Channel ( in case a user logged in twice)

    -          Secondly to group by User, Date (with a sum on channel to create the ChannelKey of 111, etc)

     

    Best Regards,

     

    Bob Duffy
    Database Architect
    Prodata | MCM (SQL Server) | MCA (SQL Server) | MVP (SQL Server) | SSAS Maestro  | Email: bob@prodata.ie | Blog: http://blogs.prodata.ie | Mob: +353 (0) 87 051 3735 | Office: +353 (1) 2933883 | Live Meeting - Meet Now

     

     

     






  • 3.  RE: Dax Query

    Posted Apr 25, 2019 03:21 PM
    Bob, Thanks for taking the time to reply, much appreciated. I'm already doing the first suggested "group by" in the PowerQuery - this gives me the number of logins per day as you suggest.

    However, based on what I think your suggesting, the second "group by" query would be problematic. Say Bill had a channel value of 101 on 01/01/2019 (per your table above), but then loged on 02/01/2019 & 03/01/2019 with a channel value of 10. For the date range 01/01/2019 to 03/01/2019 I want Bill to show up as 111, and not have 3 seperate records for 101,10 & 10 (which I now can't sum). If I use the date filter to show 02/01/2019 to 03/01/2019 I'd want to show Bill with a channel value of 10. I can't see how your suggestion achieves this.

    Thanks
    Paul

    ------------------------------
    Paul O Sullivan
    Dublin
    ------------------------------



  • 4.  RE: Dax Query

    Posted Apr 26, 2019 03:54 AM

    Hi Paul,

     

    That's the downside of pre calculating the channel mix – you lose flexibility on date grain.

     

    What date granularities do you need to calculate ?

     

    If its only 2-3 variations (say daily, weekly, monthy), then you could create three aggregate tables in Power query and use an if/switch statement to pick the appropriate table

     

    Eg  Create a Date Dimension and

    Users:=If (isfiltered(Date[Month]) =1, 'MobileAggMonth'[MonthuserCount], 'MobileAggDate'[DateUsersCount] )

     

    If you need "truly" flexible date selection then I think you'll need to calculate the channel mix in DAX at query time (not as precalculated tables in PowerQuery or calculated table), which will perform like a dog (eg using a double SUMMARIZE). Let us know and I'll grab some time at the weekend and send on example.

     

    What sort of volume do you have of data ? DAX style solutions as opposed to modelling solutions don't work so well at super high volume due to the run time grouping/scanning and non-additive nature, but at lower volume its doable.

     

    Best Regards,

    Bob

     

     

     






  • 5.  RE: Dax Query

    Silver Contributor
    Posted Apr 30, 2019 09:06 AM
    Edited by Vali Abdykarim Apr 30, 2019 09:10 AM
      |   view attached
    Hi @Paul O Sullivan,

    Dropped message to our slack channel but forgot that you can't access it while in work =D​
    I'm not sure if that's what you are trying to achieve but have a look and let me know.




    ------------------------------
    Kind Regards,

    Vali Abdykarim
    ------------------------------