Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ALEX13
Helper I
Helper I

Count occurences with several conditions.

Hi, 

I have the following challenge for Power BI:

 

I want to count the number of "Orders" with a Result different to "Realizado", but with the following conditions:

 

  • If an "IC Client" has one "Order" with a result equal to "Realizado", it shouldn´t be considered into the count.
  • if an "IC Client" has more than 3 "Orders" with a result different to "Realizado" it shouldn´t be considered into the count.

For example:

OrderIC ClientCUPFecha inicio extremaResult
1383384210000028ES0234150015711/02/2018 0:00Imposible realizar
1386439610000028ES0234150015727/02/2018 0:00Innecesario
     
1375922410000030ES0234150022316/01/2018 0:00Imposible realizar
1372734910000072ES023411gg0TJ29/12/2017 0:00Imposible realizar
1372734510000072ES023411gg0TJ30/12/2017 0:00Imposible realizar
1372812010000072ES023411gg0TJ03/01/2018 0:00Imposible realizar
1373379410000072ES023411gg0TJ05/01/2018 0:00Imposible realizar
1372208910000125ES023415257DF28/12/2017 0:00Imposible realizar
1387818410000125ES023415257DF01/03/2018 0:00Realizado

 

For this example, the count of "Orders" with the conditions would be equal to 3 ("IC client"= 10000028 with "Order"=13833842 and 13864396;"IC client"=10000030 with "Order"=13759224).

 

Thank you very much in advance.

1 ACCEPTED SOLUTION

@ALEX13

 

Also you can create a calculated Table to check the results

 

Table =
CALCULATETABLE (
    Table1,
    FILTER (
        VALUES ( Table1[IC Client] ),
        CALCULATE ( COUNT ( Table1[Result] ), Table1[Result] <> "Realizado" )
            <= 3
            && CALCULATE ( COUNT ( Table1[Result] ), Table1[Result] = "Realizado" )
                = 0
    )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

4 REPLIES 4
Zubair_Muhammad
Community Champion
Community Champion

Hi @ALEX13

 

Try this MEASURE

 

Measure =
VAR mytable =
    CALCULATETABLE (
        Table1,
        FILTER (
            VALUES ( Table1[IC Client] ),
            CALCULATE ( COUNT ( Table1[Result] ), Table1[Result] <> "Realizado" )
                <= 3
                && CALCULATE ( COUNT ( Table1[Result] ), Table1[Result] = "Realizado" )
                    = 0
        )
    )
RETURN
    COUNTROWS ( mytable )

Regards
Zubair

Please try my custom visuals

@ALEX13

 

Also you can create a calculated Table to check the results

 

Table =
CALCULATETABLE (
    Table1,
    FILTER (
        VALUES ( Table1[IC Client] ),
        CALCULATE ( COUNT ( Table1[Result] ), Table1[Result] <> "Realizado" )
            <= 3
            && CALCULATE ( COUNT ( Table1[Result] ), Table1[Result] = "Realizado" )
                = 0
    )
)

Regards
Zubair

Please try my custom visuals

Great solution, thank you very much.

It works perfectly.

@ALEX13

 

countocc.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.