Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.