Multi Value Variable: Filtering & Funnel

[acanepa]

Hello Community,

I'm trying to make a funnel with different salesforce stages. I can generate a version in a calculated table, but I want to be able to filter the funnel as well, I have a couple of filters in the dashboard, so at the moment I can only generate a static version of the funnel.

My data is like this

Lead1 email@email.com Added to Platform, Added to Salesforce, Qualified

Lead 2 email@email.com Added to Platfrom, Added to Salesforce

Lead could be repeat it. The final result is achieve something like this with different dropdown filters:

Added to the platform 6,000 (this will be the total of uniques values)

Added to Salesfroce 4,000 (This means from the 6,000 from above, 4000 were added to SF).

Qualified  3,000

The DAX function did not repeat the calculation for each group, so it will show you something like these

Added to Platfrom 1,000

Added to SalesForce 3,000

Qualified 2,000

I have tried merging queries in query editor and making calcualted tables, but I can't filter the funnels.

I have generated a table for each group and linked with a table with unique email, then linked to the original table but I didn't work.

I spent a couple hours to figure out but I could get to the result.

Regards,

[SqlJason · Accepted solution]

It would be better if you gave some examples of how your data looks and what format you need it to be. Based on what you gave, I did the following

1) Made a table with the following data and bnamed it Fact

Lead1 email@email.com	Y	Y	Y
Lead 2 email@email.com	Y	Y
Lead 3	Y
Lead 4	Y

2) Made a disconnected table for the 3 stages we have

Added to Platform

Added to SalesForce

Qualified

3) Made a measure

Cnt =
IF (
    HASONEVALUE ( Stage[Stage] ),
    SWITCH (
        VALUES ( Stage[Stage] ),
        "Added to Platform", CALCULATE ( DISTINCTCOUNT ( 'Fact'[Lead] ), 'Fact'[Added To Platform] = "Y" ),
        "Added to SalesForce", CALCULATE ( DISTINCTCOUNT ( 'Fact'[Lead] ), 'Fact'[Added to SalesForce] = "Y" ),
        "Qualified", CALCULATE ( DISTINCTCOUNT ( 'Fact'[Lead] ), 'Fact'[Qualified] = "Y" )
    ),
    DISTINCTCOUNT ( 'Fact'[Lead] )
)

It seems to work as you want

[Sean]

@SqlJason

I think we all want to know how the GIF was created - do we need an Adobe product for this?

[SqlJason]

https://screentogif.codeplex.com/

 🙂

[FrugalEconomist]

Hmmm. I'm trying to replicate the code for my data set, but it doesn't seem to be working.

I think there's something in the 'hasonevalue' or the 'switch' that's throwing sometihng off. 

 Because if I calculate it just for the individual category, it works.

[SqlJason]

It has to be a measure and not a calculated column. Could you try with a measure?

[FrugalEconomist]

Thanks! That fixed the number.

However the highlighting isn't responsive to the other objects in my dashboard.

Any ideas on what I should double check?