Filtering and Count issues

[DamienW]

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.

[DamienW]

Thanks, that seems to do the trick 🙂 

[DamienW]

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

 

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.

[MattAllington · Accepted solution]

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

[MattAllington]

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

[ntravis]

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

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