Count number of times value repeats in multiple columns

[BeginnerBI]

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!!

[WinterMist · Accepted solution]

@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.

[WinterMist · Accepted solution]

@BeginnerBI 

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

Regards,

Nathan

[WinterMist · Accepted solution]

@BeginnerBI 

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

Regards,

Nathan

[BeginnerBI · Accepted solution]

I ended doing a long query but thank you tho

[rks]

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.

[WinterMist · Accepted solution]

@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.

[IamRobin]

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

[WinterMist]

@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.

[WinterMist]

@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

[WinterMist]

@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

[BeginnerBI]

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.

[WinterMist]

@BeginnerBI 

Hopefully this is helpful to you.

Regards,

Nathan

[BeginnerBI]

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

[rks]

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.

[BeginnerBI]

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?

[BeginnerBI]

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