Power BI DAX CountIF Query

Question

I've done a lot of searching for the right answer and i can't seem to find what i'm after.

I have 2 tables in Power BI.

TableA

Table A shows the year, companies and products with a column that joins the company and product numbers together.

Table B

Table B is similar to the first table but is for a different year. In this table i do a countif to see if that company & product were seen in the year before. In Excel using

=COUNTIF(TableA[[#All],[ComProd]],[@ComProd])

Is there a similar process I can use in the PowerBI DAX/query editor to replicate this?

I've tried creating a custom column with the following in the hope that it works (it does not) -

if [ComProd] = #"TableA"[ComProd] then 1 else 0

I've also tried the following in the advanced editor:

= Table.AddColumn (
    "Count", each
      Table.RowCount(Table.SelectRows(
        #"TableA",
        each [ComProd] = [ComProd])))

But this gives me an error :

Expression.Error: 2 arguments were passed to a function which expects between 3 and 4. Details: Pattern= Arguments=[List]

Any help would be so helpful.

Thanks

You could merge matching on ComProd and count how many rows there are in each joined table. — Alexis Olson

W.B. W.B. · Accepted Answer · 2021-02-11T14:28:38

Well, I would combine those 2 tables into one, as it's more practical in the long run, but assuming that they are two tables, here's a way to do it (no relationship between two tables is assumed):

Let's assume you have a table visual based on the second table (year 2017). You would construct this measure and display in the same visual:

ComProdCount = COUNTROWS(FILTER(Table2016, Table2016[ComProd] = SELECTEDVALUE(Table2017[ComProd])))

or if it's a calculated column:

ComProdCount = COUNTROWS(FILTER(Table2016, Table2016[ComProd] = Table2017[ComProd]))

You could also create a third table that would list all unique ComProd values and create relationships to Table2016 and Table2017, then have a visual displaying ComProd from this new table and count rows in Table2016 and Table 2017.

This is suboptimal at best to have such a model, but this is a way to do explicitly what you want using DAX.

If you combined these two tables into one, this would make it much easier - you would simply create a matrix visual, put Company and Product in rows, Year in columns and count rows measure in measures.

Power BI DAX CountIF Query

1 Answers