Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi all,
I am new to the forum and new to Power-Bi and I really need a little help.
I have been given an excel sheet and asked to convert it to Power-Bi. I am really stuck with 1 formula in particular.
The excel formula is...
=COUNTIFS($G$4:$G$2000,G4,$H$4:$H$2000,1)
Range = Aisle Column
Criteria = 1st Cell In Aisle Column
Range2 = Orderlines
Criteria2 = 1
I hope that this makes sense ?
Any help greatly appreciated.
Hi @Anonymous,
This should be possible with a calculated column formula. Share the link from where i can download your file.
Hi,
Thanks guys for taking the time to have a look and try to help 🙂
Please try this link to the excel file. https://1drv.ms/x/s!AraHh-vwAl8dgU2WZDIaPRQmV0Bq
and this link for the extract from Powerbi. https://1drv.ms/x/s!AraHh-vwAl8dgVE6xMUtsW9SsAQH
I have managed to add an order line count. i still need the last 2 columns from the excel sheet adding to PowerBi .
The Table i am using in PowerBi is called Orderwell Summary.
Hi @Anonymous
Try this calculated column for "number of single picks in aisle for single item orders"
number of single picks = VAR check = COUNTROWS ( FILTER ( ALL ( Orderwell_Summary ), Orderwell_Summary[Column1] = EARLIER ( Orderwell_Summary[Column1] ) && Orderwell_Summary[no. order lines] = 1 ) ) RETURN IF ( check >= 1, check, 0 )
Hi,
That seems to work for an overall count on all records but when i use my closed date filter / slider it does not filter this new column qty. it still reads the figure for all data.
Therefore one item will have a figure like 2192 next to it when i am expecting between 1 and 5 for a given day?
Hi,
If you wish to use a slicer, then no calculated column formula approach would work because a slicer selection does not cause a calculated column to recalculate. Calculated columns are recalculated only at the time of opening the file or when clicking on Refresh.
It will help a lot if you explain the data, the business problem and show your expected result.
Hi @Anonymous
Try this calculated column for "duplicate pick locs for single item orders"
duplicate pick locs = VAR check = COUNTROWS ( FILTER ( ALL ( Orderwell_Summary ), Orderwell_Summary[Pick-Loc] = EARLIER ( Orderwell_Summary[Pick-Loc] ) && Orderwell_Summary[no. order lines] = 1 ) ) RETURN IF ( check >= 1, check, 0 )
Hi @Anonymous,
Actually, there are no concepts of cells and rows like Excel in Power BI. You can try it like below. Please post a sample here. Because we need a function like Max, Min etc. to determine the 1st cell in Aisle column.
Measure =
CALCULATE (
COUNTROWS ( Table1 ),
FILTER ( Table1, Table1[Criteria1] = max(1st cell in Aisle column) && Table[Criteria2] = 1 )
)
Best Regards,
Dale
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
75 | |
68 | |
54 | |
37 | |
35 |
User | Count |
---|---|
65 | |
65 | |
59 | |
53 | |
45 |