Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I got the following example
Provider | Project | Licitation | Order |
1 | A | X | 1 |
1 | A | - | 2 |
1 | B | Y | 3 |
2 | B | - | - |
2 | B | Z | - |
3 | A | - | 4 |
3 | C | - | 5 |
3 | C | - | 6 |
And i want a matrix/pivot table that counts for each provider how many projects/licitations/order has been involved in (not counting it therefore when a "-" appears).
The thing is that if I filter out directly these value, whenever a "-" appears for any column it will filter out all the row. Counting only the rows that contains value in all three columns (not even that because provider 3 should get all 0 based on that logic...):
PROVIDER | PROJECTS | LICITATIONS | ORDERS |
1 | 2 | 2 | 2 |
2 | 1 | 1 | 1 |
3 | 1 | 1 | 1 |
Using a new measure using =CALCULATE for each column throws an error, because for example for provider 3 if you filter out "-" on licitation you filter out everything and gets empy.
How would I fix it?
The final result should look like:
PROVIDER | # of PROJECTS | # of LICITATIONS | # of ORDERS |
1 | 2 | 2 | 3 |
2 | 1 | 1 | 0 |
3 | 2 | 0 | 3 |
Solved! Go to Solution.
@jgtirz Maybe 3 measures:
# of PROJECTS = COUNTROWS('Table') + 0
# of LICITATIONS = COUNTROWS(FILTER('Table',[Licitation]<> "-")) + 0
# of ORDERS = COUNTROWS(FILTER('Table',[Order] <> "-")) + 0
@jgtirz Maybe 3 measures:
# of PROJECTS = COUNTROWS('Table') + 0
# of LICITATIONS = COUNTROWS(FILTER('Table',[Licitation]<> "-")) + 0
# of ORDERS = COUNTROWS(FILTER('Table',[Order] <> "-")) + 0
Cheers mate, that "+0" did the trick!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |