DAX COUNT/COUNTA functions

2
votes

I've looked at many threads regarding COUNT and COUNTA, but I can't seem to figure out how to use it correctly.

I am new to DAX and am learning my way around. I have attempted to look this up and have gotten a little ways to where I need to be but not exactly. I think I am confused about how to apply a filter.

Here's the situation: Four separate queries used to generate the data in the report; but only need to use two for the DAX function (Products and Display). I have three columns I need to filter by, as follows:

  • Customer (Display or Products query; can do either)
  • Brand (Products query)
  • Location (Display query)

I want to count the columns based on if the data is unique. Here's an example:

  • Customer: Big Box Buy;
  • Item: Lego Big Blocks;
  • Brand: Lego;
  • Location: Toys;

BREAK

  • Customer: Big Box Buy;
  • Item: Lego Star Wars;
  • Brand: Lego;
  • Location: Toys;

BREAK

  • Customer: Big Box Buy;
  • Item: Surface Pro;
  • Brand: Microsoft;
  • Location: Electronics;

BREAK

  • Customer: Little Shop on the Corner;
  • Item: Red Bicycle;
  • Brand: Trek;
  • Location: Racks;

In this example, no matter the fact that the items are different, we want to look at just the customer, the brand, and the location. We see in the first two records, the customer is "Big Box Buy" and the brand is "Lego" and the location is "Toys". This appears twice, but I want to count it distinct as "1". The next "Big Box Buy" store has the brand "Microsoft" and the location is "Electronics". It appears once and only once, and thus the distinct count is "1" anyway. This means that there are two separate entries for "Big Box Buy", both with a count of 1. And lastly there is "Little Shop on the Corner" which appears just once and is counted just once.

The "skeleton" of the code I have is basically just to see if I can get a count to work at all, which I can. It's the FILTER that I think is the problem (not used in the below example) judging by other threads I've read.

TotalDisplays = CALCULATE(COUNTA(products[Brand]))

Obviously I can't just count the amount of times a brand appears as that would give me duplicates. I need it unique based on if the following conditions are met: Customer must be the same Brand must be the same Location must be the same

If so, we distinctly count it as one.

I know I ranted a bit and may seem to have gone in circles, but I was trying to figure out how to explain it. Please let me know if I need to edit this post or post clarification.

Many thanks in advance as I go through my journey with DAX!

2
dax

2 Answers

0
votes

I believe I have the answer. I used a NATURALINNERJOIN in DAX to create a new, merged table since I needed to reference all values in the same query (couldn't figure out how to do it otherwise). I also created an "unique identity" calculated column that combined data from multiple rows, but was hidden behind the scenes (not actually displayed on the report) so I could then take a measure of the unique values that way.

TotalDisplays = COUNTROWS(DISTINCT('GD-DP-Merge'[DisplayCountCalcCol]))

My calculated column is as follows:

DisplayCountCalcCol = 'GD-DP-Merge'[CustID] & 'GD-DP-Merge'[Brand] & 'GD-DP-Merge'[Location] & 'GD-DP-Merge'[Order#]

So the measure TotalDisplays now reports back the distinct count of rows based on the unique value of the customer ID, the brand, and the location of the item. I also threw in an order number just in case.

Thanks!

0
votes

I am semi new to DAX and was struggling with Count and CountA formula, you post has helped me with answers. I would like to add the solution which i got for my query: Wanted count for Right Time start Achieved hence if anyone is looking for this kind of answer use below, filter will be selecting the table and adding string which you want to

RTSA:=calculate(COUNTA([RTS]),VEO_Daily_Services[RTS]="RTSA")