Countrows with filter in 2 columns

[RvdHeijden]

I have a formula that is almost done but it doesn't work just yet.

Aantal adressen =
CALCULATE(COUNTROWS(Adressen);Table[Plaats]="value A";'Table'[AP gebied naam]="value B")

This formula works but i dont have a value A and B, the values should reference to that row.

Actually the value A en B is different in each row because Value A is value 'Plaats' en Value B is 'AP gebied naam' for that row.

How can i fix my formula so that is refers to those 2 colums for each row ?

[Greg_Deckler]

It occurs to me that this might be better handled as a measure instead of a column. But, overall this seems like a similar issue in terms of creating a calculated column based upon other rows as my MTBF article. The trick is to use EARLIER.

See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[RvdHeijden]

@Greg_Deckler

Im sorry but i dont think it has anything to do with your article, i briefly read it but it doesn't really help me in this case i think (or just dont get it, that is also possible)

Im looking for an simple anwer to the question but what would the formula be if it was a measure instead of a calculated column ?

[RvdHeijden]

@Greg_Deckler or anyone else know any solutions to this question ?

[v-yuezhe-msft]

@RvdHeijden,

I am unable to view your image as it is corrupted. Could you please share dummy data if your table and post expected result here?

Regards,
Lydia

[RvdHeijden]

Goodday @v-yuezhe-msft

The picture seems to work fine, just checked.

I need to know how many times a certain combination is in the table.

In excel it would be a sumproduct but in powerbi its a bit different.

In the example you can see that a city can be in 2 different area's (AP gebied naam) en i want to filter that.

So the formula should calculate (per Row) 

City                   AP gebied naam                

A                                 A

A                                 A

A                                 A

A                                 B

A                                 A

B                                 A

B                                 A

A                                 C

C                                 A

Result would be

City    AP gebied naam                     Count                     

A         A                                                4

A         B                                                1

A         C                                                1

B         A                                                2

C         A                                                1

Count the number of times Variable 1 and 2 are found in the same row.

[RvdHeijden]

@v-yuezhe-msft@Greg_Deckler

Any ideas ? i thought this was a simple one but guess i was mistaken 🙂 

[Floriankx]

Hello,

generally I agree to @Zubair_Muhammad, Countrows should be fine if you filter afterwards after City and gebied name.

If you want to af it in a calculated column maybe try:

COUNTROWS(FILTER(FILTER(Table;Table['AP gebied naam']=EARLIER(Table['AP gebied naam']));Table[Plaats]=EARLIER(Table[Plaats])))

So you use a filter twice to reference a table.

Best regards.

[RvdHeijden]

@Floriankx

I wanted a calculated column but in your formula it only returns 1 as a value for all rows

Aantal adressen =
COUNTROWS(FILTER(FILTER('Table';'Table'[AP gebied naam]=EARLIER('Table'[AP gebied naam]));'Table'[Plaats]=EARLIER('Table'[Plaats])))

is there something wrong with the formula ?

[Zubair_Muhammad]

@RvdHeijden

As a column...try

Column = CALCULATE(Count(Table1[ AP gebied naam]))

[Zubair_Muhammad]

Hi @RvdHeijden

Just drag the AP gebied naam column to a TABLE visual and Choose Count

see the attached file

[RvdHeijden]

@Zubair_Muhammad

what is your solution if i want it in a calculated column ?

[Floriankx · Accepted solution]

Hello,

My calculated column according to your sample given looks the following:

If I use Countrows as a Measure putting them into PivotTable gives this result:

I'm using an PowerPivot. And don't if this makes any difference.

Best regards.

[RvdHeijden]

@Floriankx in the end it did work but i made a mistake.

Your formula was based on the fact that the data was in the same table, but the data was in another table

When i changed that it worked flawless, thank you for the help