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

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