cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Melmehal Frequent Visitor
Frequent Visitor

Subtract count of each row with filter

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 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Subtract count of each row with filter

@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

 

View solution in original post

1 REPLY 1
Super User II
Super User II

Re: Subtract count of each row with filter

@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

 

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors