{"id":4074,"date":"2022-03-14T11:46:04","date_gmt":"2022-03-14T18:46:04","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=4074"},"modified":"2022-03-14T11:46:06","modified_gmt":"2022-03-14T18:46:06","slug":"how-to-distinctcount-with-filters-and-all","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=4074","title":{"rendered":"How to DISTINCTCOUNT with FILTERS and ALL"},"content":{"rendered":"\n

Questions<\/strong>:<\/p>\n\n\n\n

I needed to do a running DISTINCTCOUNT of of the number of customers (Source No_) from a transaction table (Value Entry) with 2 FILTERS (Document Type = 2 && Sales > 0)<\/p>\n\n\n\n

That was fairly easy as below;<\/p>\n\n\n\n

Active Customers = calculate(DISTINCTCOUNT(‘TABLE$Value Entry'[Source No_]), FILTER(T_Date, T_Date[Date] <= max(T_Date[Date])), FILTER(‘TABLE$Value Entry’,and(‘TABLE$Value Entry'[Document Type]=2, ‘TABLE$Value Entry'[Sales Amount (Actual)] > 0)))<\/p>\n\n\n\n

And to get total customers (for percentage of Active Customers calculation, with the same filters) i did;<\/p>\n\n\n\n

Total Active Customers = calculate(DISTINCTCOUNT(‘TABLE$Value Entry'[Source No_]), FILTER(T_Date, T_Date[Date] <= max(T_Date[Date])),FILTER(ALL(‘TABLE$Value Entry’),and(‘TABLE$Value Entry'[Document Type]=2, ‘TABLE$Value Entry'[Sales Amount (Actual)] > 0)))<\/p>\n\n\n\n

Now this work fine as long as I am applying filters in Power BI Desktop from the same Table (Value Entry), like Business Category etc<\/p>\n\n\n\n

But when I apply filters from another Table (‘Customer'[Source No_]) like sales person code or current Location code it only gives me a percentage of the TOTAL customers not the customers for that sales person code ot Current Location code.<\/p>\n\n\n\n

Solutions<\/strong><\/p>\n\n\n\n

Add measure:<\/p>\n\n\n\n

Active Customers = calculate(DISTINCTCOUNT(Records[Customer No]), FILTER(T_Date, T_Date[Date] <= max(T_Date[Date])), FILTER(Records,and(Records[Document Type]=2, Records[Sales Amount] > 0)))<\/p>\n\n\n\n

Total Active Customers = calculate(DISTINCTCOUNT(Records[Customer No]), FILTER(T_Date, T_Date[Date] <= max(T_Date[Date])),FILTER(ALL(Records),and(Records[Document Type]=2, Records[Sales Amount] > 0)))<\/p>\n\n\n\n

Total Active Customers By Customer = calculate(DISTINCTCOUNT(Customer[No]), FILTER(T_Date, T_Date[Date] <= max(T_Date[Date])),FILTER(ALL(Records),and(Records[Document Type]=2, Records[Sales Amount] > 0)))<\/p>\n\n\n\n

Ref: https:\/\/community.powerbi.com\/t5\/Desktop\/DISTINCTCOUNT-with-FILTERS-and-ALL\/m-p\/64811#M26794<\/p>\n\n\n\n

Some additional samples<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

Questions: I needed to do a running DISTINCTCOUNT of of the number of customers (Source No_) from a transaction table (Value Entry) with 2 FILTERS (Document Type = 2 && Sales > 0) That was fairly easy as below; Active Customers = calculate(DISTINCTCOUNT(‘TABLE$Value Entry'[Source No_]), FILTER(T_Date, T_Date[Date] <= max(T_Date[Date])), FILTER(‘TABLE$Value Entry’,and(‘TABLE$Value Entry'[Document Type]=2, ‘TABLE$Value Entry'[Sales Amount Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,621],"tags":[1136,1135],"class_list":["post-4074","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-power-bi","tag-distinctcount-with-all","tag-distinctcount-with-filters"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/4074","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4074"}],"version-history":[{"count":2,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/4074\/revisions"}],"predecessor-version":[{"id":4078,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/4074\/revisions\/4078"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4074"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4074"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4074"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}