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...
Need Help
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.