Forum Discussion
Multi Value Variable: Filtering & Funnel
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] Y Y Y Lead 2 [email protected] 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
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] | Y | Y | Y |
| Lead 2 [email protected] | 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
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.
