Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chernni
Frequent Visitor

CALCULATE, DISTINCTCOUNT and FILTER probable bug

Hi,

 

I`m quite new to PowerBI and need some help on something... I`ve been trying to use a combination of three formulas, CALCULATE, DISTINCTCOUNT and FILTER, all at once to achieve the following: count the unique number of customers according to each ITEM.ID in a given table.

 

I have a table `db ORDERS`, where I can find multiple items (ITEM.ID column) per customer, in different dates (not shown here)... What i`m trying to figure is, which items have unique customers and which ones don`t? But, in the same `db ORDERS` i have two types of orders, "Venda" and "Compra" in the column ORDER.TYPE, and I only want to analyse "Venda", so before analyzing unique counts I need to filter my data... WIth that in mind i`ve create the following measure:

 

 

COUNT.CUSTOMERS = CALCULATE(DISTINCTCOUNT('db CUSTOMERS'[CUSTOMER]),
FILTER('db ORDERS','db ORDERS'[ORDER.TYPE] = "Venda"))

 

But that`s giving me the wrong number. For example, the table below, the last record, is being ignored for some reason... As in other cases... sometimes the last, sometimes some record in the middle, sometimes it count correctly. Can someone help me figuring this out? Thanks !!

Image_test.jpg

2 REPLIES 2
Phil_Seamark
Employee
Employee

Hi @chernni

 

Are you sure that your customer "Pricila" has rows in your CUSTOMERS table that have an ORDER.TYPE of "Venda" for item ID 1201?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thansk for the reply @Phil_Seamark. Yes, I do. Check the image below, that`s my table with the filter for "Priscila" and "1201".

 

table.jpg

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.