Hi everyone!
I cannot, by all means, disclosure the data involved because it is highly sensitive, so i'll try to explain my problem the best i can.
I'm trying to build a report in power bi desktop, in wich i need to count the number of elements that match a certain criteria given by a calculated measure.
Below is a image of the matrix visual i'm trying to obtain, where all the values are calculated measures (6 measures in total).
The bussiness scenario is the following:
For each suplyer that has trips done in a given month and a given route, i would like to count the number of elements of that supplier that were productive, acording to the calculated measure identified in column "Element Productivity". This measure retrives 2 values: "OK" or "NOK" and an element is productive when the measure equal to OK in this filter context: Route, Suplier, Year, Month.
Even though i'm not displaying it here, my data model is composed, amongst others, with the folowing tables:
Table 1- date/calendar table,
Table 2 - fact table with all the trips of a specific subset of supliers
Table 3 - fact table with all the trips of all elements of all supliers ( this table serves as a bridge table, between table 1 and table 3 given that a given trip has a single date)
So, can anyone help me build a 7th measure that counts the "OK" elements in this context, pls?
Btw, I'v been searching arround and I didn't found a specific answer to this kind of problem in power bi, that's why i opened this thread.
Thanks in advance. 🙂
Hi,
i'm sorry for the late response, but i haven't had the opportunity to answer sooner.
Anyway, about the problem at hand:
I built a powerbi desktop file with some sample dummy data, that resembles the problem at hand.
Below is a link with the file:
https://drive.google.com/open?id=1ejj3tDGIt1mpGjzk8DVt6njsv7OvZxRF
So, here is my data model:
I'v simplified things, relatively to my first post (i hope) and i would like to build a measure that returns the following:
For each supplier and for each month, the percentage of vehicles that have done the contracted km's or more. The contracted Km's are indicated in the table "dimVehicles".
More, i would like to have a slicer that allows to filter by the suppliers that had X% of vehicles that have done the contracted Km's or more.
The dashboard i'd like to obtain is this one (It's missing the measure i'm having problem calculating, witch is represented in red on the chart as an example, and the slicer that will allow to get the suppliers that have certain percentage of vehicles that have done the contracted km's or more):
The measure present on each visual, is this one:
VehiclesOnService = COUNTROWS( VALUES( facServices[Veichle] ) )
I'v tried an aproach where i was using summarize tables, but that aproach wasn't good enough, because i would like to use a disconected table that contains a margin of tolerance of the Km's done by each vehicle. And with this disconnected table, i would like to build a slicer that influence the measure that I'v tried to explain before .
I hope this explanation is clear enough, but if not, please let me know what needs more clarification.
Thank you! 🙂
