Forum Discussion

DamienW's avatar
DamienW
Frequent Visitor
9 years ago
Solved

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.

 

 

 

 

 

Need Help
  • MattAllington's avatar
    MattAllington
    9 years ago

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

5 Replies

  • ntravis's avatar
    ntravis
    Frequent Visitor
    9 years ago

    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

  • MattAllington's avatar
    MattAllington
    Community Champion
    9 years ago

    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

  • DamienW's avatar
    DamienW
    Frequent Visitor
    9 years ago

    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's avatar
      MattAllington
      Community Champion
      9 years ago

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