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

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
    Icon for Community Champion rankCommunity 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))