Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Countif in Power BI

Hi all,   I have a table in Power BI that has the concat of order number and order line and a total net value assigned for each combination. The problem is that the same combination of order number...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

     

    What amitchandak said is like this:

     

    1. Add an Index column in Power Query Editor.

     

    2. Create a column in Power BI Desktop Data view.

    Column =
VAR MinIndex =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER (
            'Table',
            'Table'[Order no] = EARLIER ( 'Table'[Order no] )
                && 'Table'[Order line] = EARLIER ( 'Table'[Order line] )
        )
    )
RETURN
    IF ( 'Table'[Index] = MinIndex, 'Table'[Value] )

     

     

    BTW, .pbix file attached.

     

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous , You have add a index colum and then add then find the min for order no , order using earlier and put value there

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi amitchandak can you please explain the answer again?
I didn't get your answer.

Thank you!

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    What amitchandak said is like this:

     

    1. Add an Index column in Power Query Editor.

     

    2. Create a column in Power BI Desktop Data view.

    Column =
VAR MinIndex =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER (
            'Table',
            'Table'[Order no] = EARLIER ( 'Table'[Order no] )
                && 'Table'[Order line] = EARLIER ( 'Table'[Order line] )
        )
    )
RETURN
    IF ( 'Table'[Index] = MinIndex, 'Table'[Value] )

     

     

    BTW, .pbix file attached.

     

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Icey thank you SO much for your help. It finally worked!! 🙂