Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
37 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
27 | |
24 | |
23 |