Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BeginnerBI
Helper I
Helper I

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
Impactful Individual
Impactful Individual

@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
Impactful Individual
Impactful Individual

@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

14 REPLIES 14
WinterMist
Impactful Individual
Impactful Individual

@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
Impactful Individual
Impactful Individual

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

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
Impactful Individual
Impactful Individual

@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_0-1707408919408.png

 

WinterMist
Impactful Individual
Impactful Individual

@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
Impactful Individual
Impactful Individual

@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
Impactful Individual
Impactful Individual

@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
Helper I
Helper I

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors