cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BeginnerBI
Frequent Visitor

Count number of times value repeats in multiple columns

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

3 ACCEPTED SOLUTIONS
WinterMist
Responsive Resident
Responsive Resident

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

View solution in original post

WinterMist
Responsive Resident
Responsive Resident

@BeginnerBI 

 

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

 

Regards,

Nathan

View solution in original post

I ended doing a long query but thank you tho

View solution in original post

12 REPLIES 12
WinterMist
Responsive Resident
Responsive Resident

@BeginnerBI 

 

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

 

Regards,

Nathan

I ended doing a long query but thank you tho

rks
Resolver I
Resolver I

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
Responsive Resident
Responsive Resident

@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
Responsive Resident
Responsive Resident

@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
Responsive Resident
Responsive Resident

@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

 

WinterMist_0-1657912841684.png

 

WinterMist_1-1657912881316.png

 

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
Responsive Resident
Responsive Resident

@BeginnerBI 

 

WinterMist_1-1657909358139.png

 

 

WinterMist_0-1657909305886.png

 

Hopefully this is helpful to you.

 

Regards,

Nathan

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
Resolver I
Resolver I

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.

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
Frequent Visitor

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Dev Camp Session 30

Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors