cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

DAX COUNT/COUNTA filtered by columns

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper IV
Helper IV

Re: DAX COUNT/COUNTA filtered by columns

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!

View solution in original post

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: DAX COUNT/COUNTA filtered by columns

@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]
        )
    )
)

DAX COUNT COUNTA filtered by columns.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper IV
Helper IV

Re: DAX COUNT/COUNTA filtered by columns

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!

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors