Subtract count of each row with filter

[Melmehal]

Hi Everyone 

I have a column "A" that has a bunch of id's, i want to be able to count that column "A" with a specific filter from Column "B" , and group it by Column "C" is employee ID.  Example:

Table with B filter = 6 :  

A        B      C 

1         6      122

2         6     122 

3         6      122 

Result with initial count: 

A       B      C

3       6       122 

I then want to be able to subtract that information from column "A"   with the above filters and count 

by the same table but when my column "B" is set at a different filter. For example

A      B      C 

1       9      122 

2        9      122  

so with the count that would be:  A    B    C  

                                                      2     9     122 

My final desired result would be a subtraction of the count of column "A" with specific filter "B" = 6  

minus

count of column "A" with specific filter "B" = 9  

SO final answer is    

A       B      C

3       6       122 

-  

A      B      C 

2      9      122 

= 

A      B      C 

1       ?        122  

B is my status filter, not sure what it would be in this case. 

I also want to be able to filter all the above info with my date table, to track it daily

Thanks for help in advance 

[Anonymous · Accepted solution]

@Melmehal  - I think you'll want to start by creating a disconnected parameter table. This means that the new table will have no relationship to the current table(s). It could be something like this:

Parameters = 
var B_Vals = DISTINCT('YourTable'[B])
return CROSSJOIN(
    SELECTCOLUMNS(B_Vals ,"Filter1",[B]),
    SELECTCOLUMNS(B_Vals ,"Filter2",[B])
)

Then, your measure:

Your Measure = 
var B1filter = SELECTEDVALUE('Parameters'[Filter1])
var B2filter = SELECTEDVALUE('Parameters'[Filter2])
var A1 = IF(ISBLANK(B1filter),0,CALCULATE(COUNTROWS('YourTable'), 'YourTable'[B] = B1filter))
var A2 = IF(ISBLANK(B2filter),0,CALCULATE(COUNTROWS('YourTable'), 'YourTable'[B] = B2filter))
return A1-A2

Hope this helps,

Nathan