March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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!!
Solved! Go to Solution.
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.
Hope you are well. Was the PBIX helpful to you? Were you able to resolve the problem?
Regards,
Nathan
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
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.
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
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.
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
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
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.
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
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?
Probably what I need is a powerquery formula that makes this function as well.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
16 | |
15 | |
12 | |
11 |
User | Count |
---|---|
32 | |
26 | |
24 | |
20 | |
14 |