Help make up funnel and filter only those values which passed through a certain stage

[tim8ska]

Good day all,

Can't correctly make up my web search query, so will try here.

I am novice to DAX and PBI, but feel ok with SQL and Relationships.

Part 1.

I have a list of car service labours, where I have a grouped all labours to Service Intervals "TO 0", "TO 1", "TO 2", "TO 3" etc. and ServiceOrderID.

Another table contains ServiceOrdersID, Vehicle VINs.

So what I want to make is a funnel of each Service Interval, but only of those vehicles, who passed throught stage "TO 0". I do not want to see vehicles, that start from "TO 3" for example, as my table conains only data from year 2015.

Here is the funnel I want to achieve.

Table 1 sample 'Labours'

Table 2 sample 'ServiceOrders' with ServiceOrderID and VIN codes for vehicles and other info.

And I don't know where to start from. I made up a mockup in Excel and here it is if it helps at all:

In the column G I joined [Group] and VIN from lookup via ServiceOrderID. Column H is a VIN lookup.

Column I checks if there are values in column G, that contain "TO 0 " & [VIN]. Then I would show in my funnel only vehicles that are TRUE. However, I need to take dates of "TO 0" into account, see part 2.

From the sample table from above I have only 5 VINs that contain "TO 0" value, so I will need to show these VIN's on the next stages "TO 1", "TO 2", "TO 3" etc.

Here is the funnel I came up with from the sample data.

Part 2.

Now I need to put a date slicer, which filters out the "TO 0" dates to be counted in the TRUE/FALSE above to be shown in the funnel.

[amitchandak · Accepted solution]

@tim8ska , Assume you have a measure - measure. Then you can try measures like

T0 = calculate(count(Labour[service Order ID]), filter(Table, table[group] = "TO 0"))

New measure = sumx(filter(addcolumns( summarize( Labour, ServiceOrders[Car VIN]), "_stage", [T0], "_1", [Measure]), not(isblank([_stage]))), [_1])

[tim8ska]

Thank you for your contribution. Did not check if your suggestion works as intended, as I went a little different route using SQL.

First I joined the VIN numbers into the Labour table, so that I have one table for personal comfort.

Then I creted a separate table for distinct VINs, where [Group] = "TO 0".

When adding these to Power Bi Desktop, made them related via VIN, I then added the main Labour table to the funnel with [Groups] showing in the Y axis. Then added filter [VIN]s from the new distinct table and filtered those that aren't blank.

Visualization shows as expected.

Thanks again.