cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DamienW Frequent Visitor
Frequent Visitor

Filtering and Count issues

Hi, new to DAX and Powerbi, 

 

I have two tables in a 1:M relationship:

 

TableA (1)

- GUID (key)

- Name

 

TableB: (M)

- ID (key)

- GUID

- TYPE

 

Where type can be 1 or 2

 

I am trying to develop measures to calcaulate Types: "1 only", "2 only"  or a "1 AND 2"

 

I have used the following measures to determine # of Type "1 only" (hopefully the values are correct), but struggling to get the "1 AND 2" to work.

TYPE1 ONLY:

 

 

CALCULATE(
  COUNTAX(TableB,TableB[GUID],
  DISTINCT(TableB[GUID]),
  FILTER(TableB,TableB[type]=1)
  )
 

 

 

I used the same formula above for 2 only by changing the the filter to Type = 2

 

However when trying to do this for Both, I get an Zero results (which I know to be incorrect) I was trying:

 

Spoiler
CALCULATE(
  COUNTAX(TableB,TableB[GUID],
  DISTINCT(TableB[GUID]),
  FILTER(TableB,TableB[type]=1),
FILTER(TableB,TableB[type]=2)
)

 

I have also tried combinations of FILTER as 

FILTER(TableB,TableB[type]=1 && TableB[type]=2) which does not seem to work...

 

Any suggestion as to where I am maybe going wrong? 

 

Thanks in advance.

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Filtering and Count issues

OK, how about this

 

Count of Records = countrows(TableB)

 

Count of any type 1 = calculate([count of records],TableB[Type]="1")

Count of any type 2 = calculate([count of records],TableB[Type]="2")

Count only type 1 =SUMX(TableA,if([Count of any type 1] >0 && [Count of any type 2] = 0,1))

Count only type 2 =SUMX(TableA,if([Count of any type 1] =0 && [Count of any type 2] > 0,1))

Count always both  =SUMX(TableA,if([Count of any type 1] >0 && [Count of any type 2] > 0,1))



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
5 REPLIES 5
ntravis Frequent Visitor
Frequent Visitor

Re: Filtering and Count issues

Have you tried removing both filters?  I am not sure what your end goal / purpose is, but if you use:

 

Both = CALCULATE(
COUNTAX(TableB,TableB[GUID]),
DISTINCT(TableB[GUID])
)

 

 

 

CaptureTeST.PNG

If this works, please marked as accepted solution, if not please let me know what your anticipated values would be and we can work at getting there.

 

Thanks

Highlighted
Super User
Super User

Re: Filtering and Count issues

I think you are making this harder than it needs to be :-).  Try this

 

Count of Records = countrows(TableB)

This will count both types

 

Count of type 1 = calculate([count of records],TableB[Type]="1")

Count of type 2 = calculate([count of records],TableB[Type]="2")

 

If you want to learn quickly in a structured way, you may like to consider reading my book - I am sure it will help you



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
DamienW Frequent Visitor
Frequent Visitor

Re: Filtering and Count issues

Thanks, Sorry some context may help.

Table A is a lead table

Table B is a Cart table

 

Scenarios that can occur:

lead could have type 1 product

lead could have type 2 product (or mutiple of)

lead coud have type 1 and type 2 products

 

 2016-11-25_08h46_17.png

 

So I am trying to determine the number of leads with:

Type 1 products only

Type 2 products only 

Both Type 1 and Typ2

 

Hope this clears things up.

Super User
Super User

Re: Filtering and Count issues

OK, how about this

 

Count of Records = countrows(TableB)

 

Count of any type 1 = calculate([count of records],TableB[Type]="1")

Count of any type 2 = calculate([count of records],TableB[Type]="2")

Count only type 1 =SUMX(TableA,if([Count of any type 1] >0 && [Count of any type 2] = 0,1))

Count only type 2 =SUMX(TableA,if([Count of any type 1] =0 && [Count of any type 2] > 0,1))

Count always both  =SUMX(TableA,if([Count of any type 1] >0 && [Count of any type 2] > 0,1))



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
DamienW Frequent Visitor
Frequent Visitor

Re: Filtering and Count issues

Thanks, that seems to do the trick Smiley Happy