DAX COUNT/COUNTA filtered by columns

[jderekc]

I am new to DAX and am learning my way around, so please pardon me if I ask several questions about it.  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
  • Brand
  • Location
• 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
  • ---
  • Customer: Big Box Buy
  • Item: Lego Star Wars
  • Brand: Lego
  • Location: Toys
  • ---
  • Customer: Big Box Buy
  • Item: Surface Pro
  • Brand: Microsoft
  • Location: Electronics
  • ----
  • 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:

1. Customer must be the same
2. Brand must be the same
3. 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!

Regards,

Derek

[v-jiascu-msft]

@jderekc,

Hi Derek,

The table visual brings the distinct values together by default. So we can count the rows with any column of the three like this:

Measure 1 =
DISTINCTCOUNT ( Table1[Customer] )

But the total value could be wrong. The upgraded formula would be:

Measure 2 =
COUNTROWS (
    DISTINCT (
        SELECTCOLUMNS (
            Table1,
            "Barnd", [Brand],
            "Location", [Location],
            "Customer", [Customer]
        )
    )
)

Best Regards!

Dale

[jderekc · Accepted solution]

Thanks!  You got me on the right track.  It didn't quite work, but I was able to figure it out:

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!