Forum Discussion

acanepa's avatar
acanepa
Resolver I
9 years ago
Solved

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 protected] Added to Platform, Added to Salesforce, Qualified

Lead 2 [email protected] 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,

 

 

  • 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 protected]YYY
    Lead 2 [email protected]YY 
    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

6 Replies

  • SqlJason's avatar
    SqlJason
    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 protected]YYY
    Lead 2 [email protected]YY 
    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