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

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