Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Problem with Count

Hi Everyone, I am new to Power BI and I have a count problem. In the table below, I am trying to create an analysis of how the delivery days of the following products have changed from Jan - May. Th...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi FrankAT,

 

Thanks for the message. However, I am not trying to count the delivery days. I want to count the product that have more than one delivery days value. For instance, Product A has 2 values in delivery days (i.e. 60 and 90), while Product B has only 1 value in delivery days (i.e. 60).

 

FrankAT 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Update: We can ignore the months columns as I can see that it is causing some comfusion.

mahoneypat FrankAT 

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous 

    is this what you are looking for:

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    Please try an expression like this

     

    Products with >1 Dates =
    COUNTROWS (
        FILTER (
            VALUES ( table[product] ),
            CALCULATE (
                DISTINCTCOUNT ( table[delivery days] )
            ) > 1
        )
    )

     

    Regards,

    Pat

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Pat,

      Thanks for your effort. The measure was able to calculate the sum of Products with more than one delivery days value, however, I could not identify the products that were counted when i used the formula. This is only a dummy data, my original data has thousands of rows, and I want to know both the number of products and the name of products that has more than one delivery days value. It seems like an easy task but surprisingly it is more complicated that it looks. 

       

      Do you have any other ideas? I appreciate your help.

       

      mahoneypat FrankAT 

       

       

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        5 years ago

        To do that, you could make a table visual with your Product column and this measure.  Products with only 1 delivery day value would be blank and get filtered out from the visual.

         

        Count if Multiple =
        VAR deldays =
            DISTINCTCOUNT ( Table[Delivery Days] )
        RETURN
            IF (
                deldays > 1,
                deldays,
                BLANK ()
            )

         

        Regards,

        Pat