Forum Discussion

shiyip's avatar
shiyip
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

DAX: measure with filtering?

Hi,

 

Each row of my table is a customer purchase transaction. Here are my columns:

 

Customer ID----this is the unique identifier for each customer. A customer can have multiple rows in my table.

Web Site----the web site the customer visited. There are only two websites.

Web Page----the web page the customer viewed. There are thousands of web pages. One web page can only belong to one web site.

Product----what did the custome buy. There are total 4 products.

Days_diff----the number of days between visiting a web page and making a purchase. The numbers are integers, could be positive or negative, ranging from -20 to +20.

 

I need to create a top% measure which equals the distinctcount(customer id) for a web page divided by the total distinctoucnt(customer id) for that web site which the web page belongs to. I also need the measure to work when I filter by each product or all products.

 

Below is my current top% measure. Notice the demoniators is hardcoded as 6490 and 2484. They are the total  numbers of unique customers who visited web site abc.com and xyz.com. I need to turn them into dynamic measure so I can filter by product. How to do it? 

Top% = CALCULATE(DISTINCTCOUNT(table[customer_id]),table[web_site]="www.abc.com")/6490+CALCULATE(DISTINCTCOUNT(table[customer_id]),table[web_site]="www.xyz.com")/2484

 

 
I also need to create measures for the 1st, 2nd, 3rd quartiles of days_diff and have the ability to cross filter those by web page and product. Eventually I am listing the 1st, 2nd, 3rd quartile for each web page, and using the top% as my cutoff, and filterting by product. Thank you so much.
1st quartile= percentile.exc(table[days_diff].025)
Need Help

2 Replies

  • v-caliao-msft's avatar
    v-caliao-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    Hi Shiyip,

     

    In your scenario, you need to calculate distinctcount(customer id) for a web page divided by the total distinctoucnt(customer id) for that web site, right?

     

    Based on understanding, you have Web Site and Web Page columns in your table, so for the distinctcount(customer id) for a web page, you can use the expression
    DistinctCountForPage = CALCULATE(DISTINCTCOUNT(table[customer_id]),table[web_site]="PageName")
    And for the total distinctoucnt(customer id) for that web site, you can use the expression
    DistinctCountForSite = CALCULATE(DISTINCTCOUNT(table[customer_id]),table[web_site]="SiteName")

    Then the top% measure could like below
    Top% = CALCULATE(DISTINCTCOUNT(table[customer_id]),table[web_site]="PageName")/CALCULATE(DISTINCTCOUNT(table[customer_id]),table[web_site]="SiteName")

     

    If I have anything misunderstood, please point it out and elaborate your requirement, so that we can make further analysis.

     

    Regards,

    • shiyip's avatar
      shiyip
      Icon for Microsoft Employee rankMicrosoft Employee
      10 years ago

      Thanks v-caliao-msft for the suggestion.

       

      Top% = CALCULATE(DISTINCTCOUNT(table[customer_id]),table[​web_site]="PageName")/CALCULATE(DISTINCTCOUNT(tabl​e[customer_id]),table[web_site]="SiteName")

       

      Page_Name is the column name. Within that column, there are thousands of values. Each value represents a pagename, e.g. redsofamarketingpage. In your DAX code, I don't want to hardcode a specific single page. I want it work for all pages.

      How should the DAX code be modified to accomodate that? Thanks.