topic Re: How to replicate Excel COUNTIF formula? in DAX Commands and Tips

How to replicate Excel COUNTIF formula?

JollyRoger01 — Sat, 03 Jul 2021 04:33:11 GMT

I am struggling to get my head around the basic layout of DAX, even a simple COUNTIF formula. I have two tables in my data model connected by a field called System. I just wanted to calculate in this table how many rows are in Table 1 where the two row values for System are the same.

Re: How to replicate Excel COUNTIF formula?

Fowmy — Sat, 03 Jul 2021 07:37:31 GMT

JollyRoger01

I guess you have a one-to-many relationship from this table to table1. Add the following calculated column to the table on the one side

Count System = CALCULATE( COUNTA(Table1[System]), RELATEDTABLE(Table1) )

Re: How to replicate Excel COUNTIF formula?

JollyRoger01 — Sat, 03 Jul 2021 08:36:26 GMT

Thank you, that seems to work. Though I am a little confused as to what the function is actually doing. How does it know that I am trying to match the two columns? Is that implied from the one to many relationship already?

Re: How to replicate Excel COUNTIF formula?

Fowmy — Sat, 03 Jul 2021 08:47:24 GMT

JollyRoger01

Yes, since you already have a valid relationship, CALCULATE activates it here.

Re: How to replicate Excel COUNTIF formula?

JollyRoger01 — Sat, 03 Jul 2021 10:02:32 GMT

Okay, I understand that. But how would I do this if I didn't have a relationship?

Re: How to replicate Excel COUNTIF formula?

Fowmy — Sat, 03 Jul 2021 10:40:42 GMT

JollyRoger01

You can do it this way:

Count System = VAR __system = [System] RETURN COUNTROWS ( FILTER ( ALL ( Table1[System] ), Table1[System] = __system ) )

Re: How to replicate Excel COUNTIF formula?

JollyRoger01 — Mon, 05 Jul 2021 04:42:23 GMT

I can't seem to make that one work. Are you sure that is in the right format?