cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
acanepa
Resolver I
Resolver I

Multi Value Variable: Filtering & Funnel

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,

 

 

1 ACCEPTED SOLUTION
SqlJason
Memorable Member
Memorable Member

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.comYYY
Lead 2 email@email.comYY 
Lead 3Y  
Lead 4Y  

 

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

temp.gif

View solution in original post

6 REPLIES 6
SqlJason
Memorable Member
Memorable Member

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.comYYY
Lead 2 email@email.comYY 
Lead 3Y  
Lead 4Y  

 

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

temp.gif

View solution in original post

Sean
Community Champion
Community Champion

@SqlJason

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

 

 

 

SqlJason
Memorable Member
Memorable Member

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. 

Capture1.JPG

 

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

 

 

 

 

 

 

 

 

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

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?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.