Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
User | Count |
---|---|
131 | |
71 | |
70 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
65 | |
62 | |
53 |