Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Help Converting an Excel countifs function into Power BI

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.

 

 

7 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi Anonymous,

     

    This should be possible with a calculated column formula.  Share the link from where i can download your file.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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.

       

       

       

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        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 )