topic Count number of times value repeats in multiple columns in DAX Commands and Tips

Count number of times value repeats in multiple columns

BeginnerBI — Fri, 15 Jul 2022 13:38:05 GMT

Hello guys!

Thank you in advance for support us one each other, no one know everything and funny fact even the most simple things.

I'm looking to translate this formula: =COUNTIF(D11:X11,28) in Power BI

As you can see I want to see how many times the number '28' (which is a code) is repeated in different columns.

Please please I'm a new hire and this is freaking me out hahahha.

Thank you!!

Re: Count number of times value repeats in multiple columns

BeginnerBI — Fri, 15 Jul 2022 13:53:30 GMT

Probably what I need is a powerquery formula that makes this function as well.

Re: Count number of times value repeats in multiple columns

rks — Fri, 15 Jul 2022 14:08:04 GMT

I don't know the model, but generally a CALCULATE(COUNTROWS(table), CodeColumn = 28) should give you the number of rows containing a 28 as a code.

Re: Count number of times value repeats in multiple columns

BeginnerBI — Fri, 15 Jul 2022 15:29:42 GMT

Hello rks thank you so much, but what I need to know is how many columns in the same record (row) have the code '28'. Can you please advise?

Re: Count number of times value repeats in multiple columns

WinterMist — Fri, 15 Jul 2022 18:23:08 GMT

BeginnerBI

Hopefully this is helpful to you.

Regards,

Nathan

Re: Count number of times value repeats in multiple columns

WinterMist — Fri, 15 Jul 2022 19:22:42 GMT

@BeginnerBI

My apologies. The previous code will remove rows from the table visual which do not return a value for the measure. In other words, if the measure does not find a "28" in all 3 columns, then that row gets excluded from the table visual.

To avoid this, just wrap each of the Column search VAR's within a COALESCE, to force it to return a 0 if no "28" is found.

As you can see from the screenshot below, the 3rd row does not have a 28 in any of the 3 columns (7, 4, 4).

Previously, this row was excluded. Now, it is included.

Regards,

Nathan

Re: Count number of times value repeats in multiple columns

BeginnerBI — Fri, 15 Jul 2022 19:23:37 GMT

Hi Nathan,

Thank you so much. I'm not sure what I did wrong but it runs but return the value in blank

OcurrencesCode15 =

VAR _Code = 15

VAR _code1 =

CALCULATE(

COUNTROWS('Test'),

'Test'[code1] = _Code

)

VAR _code2 =

CALCULATE(

COUNTROWS('Test'),

'Test'[code2] = _Code

)

VAR _code3 =

CALCULATE(

COUNTROWS('Test'),

'Test'[code3] = _Code

)

VAR _Total_OcurrenceCode15 =

IF(

HASONEVALUE(Test[code1]),

SUMX(

'Test',

_code1 + _code2 + _code3

)

RETURN

_Total_OcurrenceCode15

Re: Count number of times value repeats in multiple columns

WinterMist — Fri, 15 Jul 2022 19:30:58 GMT

BeginnerBI

It looks like we both posted at about the same moment.

Like I mentioned, it was my mistake. Just wrap those 3 VARs for _Code1, _Code2 & _Code3 within COALESCE as shown, and that will force each VAR to return 0 when the target value is not found, and those rows should remain in the output.

Regards,

Nathan

Re: Count number of times value repeats in multiple columns

BeginnerBI — Fri, 15 Jul 2022 19:53:24 GMT

Nathan,

What you did is beatiful and exactly what I am trying to do, but curiously it's no values at all...this is frustrating 😞 but thank you so much.

Re: Count number of times value repeats in multiple columns

WinterMist — Fri, 15 Jul 2022 19:58:48 GMT

BeginnerBI

Here is the link to my PBIX.

Please take a detailed look & let me know if it helps.

https://drive.google.com/file/d/1-_3G72wA7Ta7Q_riF9v3GErY_kUKWGOE/view?usp=sharing

Regards,

Nathan

P.S. If my PBIX is not helpful to you, please share the link to your PBIX and I will investigate further.

Re: Count number of times value repeats in multiple columns

rks — Mon, 18 Jul 2022 14:28:13 GMT

I believe complex DAX results from a model that can still be optimized.

Using PowerQuery could be a first step. Given the screenshot from above I would try to unpivot the columns code1, code2, code3 into a single column with the given values.

Applying a filter on one column is much easier.

Again, all tips would be more useful if you'd share the data-model.

Re: Count number of times value repeats in multiple columns

WinterMist — Mon, 25 Jul 2022 21:10:35 GMT

BeginnerBI

Hope you are well. Was the PBIX helpful to you? Were you able to resolve the problem?

Regards,

Nathan

Re: Count number of times value repeats in multiple columns

BeginnerBI — Tue, 26 Jul 2022 10:42:06 GMT

I ended doing a long query but thank you tho

Re: Count number of times value repeats in multiple columns

IamRobin — Tue, 02 Jan 2024 19:34:18 GMT

Hi Nathan, I wanted to have a look at your solution, but your PBIX google link appearently doesn't exist anymore. I have a simular problem

Btw, I don't normally use Google so if you could just post the PBIX solution here that would be great.

Thanks in advance,

Robin

Re: Count number of times value repeats in multiple columns

WinterMist — Thu, 08 Feb 2024 16:17:09 GMT

IamRobin

Sorry for the late response.

Yeah, the screenshot solution is in the thread above, but pasted here again as well for your convenience.

Hope this is helpful to you.

Nathan

P.S. You can't upload PBIX files to this forum. You have to upload them somewhere else, and then share the link in forums.