Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
For example:
Order | IC Client | CUP | Fecha inicio extrema | Result |
13833842 | 10000028 | ES02341500157 | 11/02/2018 0:00 | Imposible realizar |
13864396 | 10000028 | ES02341500157 | 27/02/2018 0:00 | Innecesario |
13759224 | 10000030 | ES02341500223 | 16/01/2018 0:00 | Imposible realizar |
13727349 | 10000072 | ES023411gg0TJ | 29/12/2017 0:00 | Imposible realizar |
13727345 | 10000072 | ES023411gg0TJ | 30/12/2017 0:00 | Imposible realizar |
13728120 | 10000072 | ES023411gg0TJ | 03/01/2018 0:00 | Imposible realizar |
13733794 | 10000072 | ES023411gg0TJ | 05/01/2018 0:00 | Imposible realizar |
13722089 | 10000125 | ES023415257DF | 28/12/2017 0:00 | Imposible realizar |
13878184 | 10000125 | ES023415257DF | 01/03/2018 0:00 | Realizado |
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.
Solved! Go to Solution.
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 ) )
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 )
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 ) )
Great solution, thank you very much.
It works perfectly.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |