Hi All,
A follow up to a previous thread that I had accepted an answer for...
https://community.powerbi.com/t5/Desktop/Filter-out-a-certain-value-but-use-that-value-in-a-slicer/m...
I would like to do as above but add an additional value that needs filtering out
Total Leads =
IF (
SELECTEDVALUE ( 'Lead'[Enquiry Source]) = "Verso Group",
COUNTROWS ( FILTER ( 'Lead', 'Lead'[Enquiry Source] = "Verso Group")),
COUNTROWS ( FILTER ( 'Lead', 'Lead'[Enquiry Source] <> "Verso Group"))
)Total Qualifed Leads =
IF (
SELECTEDVALUE ( 'Lead'[Enquiry Source] ) = "Verso Group",
COUNTROWS ( FILTER ( 'Lead', 'Lead'[Enquiry Source] = "Verso Group" &&'Lead'[statecode] = 1 )),
COUNTROWS ( FILTER ( 'Lead', 'Lead'[Enquiry Source] <> "Verso Group" && 'Lead'[statecode] = 1) )
)Total Disqualifed =
IF (
SELECTEDVALUE ( 'Lead'[Enquiry Source] ) = "Verso Group",
COUNTROWS ( FILTER ( 'Lead', 'Lead'[Enquiry Source] = "Verso Group" && 'Lead'[statecode] = 2 )),
COUNTROWS ( FILTER ( 'Lead', 'Lead'[Enquiry Source] <> "Verso Group" && 'Lead'[statecode] = 2 ) )
)Total Disqualifed No Spam & Dupe =
IF (
SELECTEDVALUE ( 'Lead'[Enquiry Source] ) = "Verso Group",
COUNTROWS (
FILTER (
'Lead',
'Lead'[Enquiry Source] = "Verso Group"
&& 'Lead'[statecode] = 2
&& ('Lead'[new_disqualifiedreason] = 279640000
|| 'Lead'[new_disqualifiedreason] = 279640002
|| 'Lead'[new_disqualifiedreason] = 279640004)
)
),
COUNTROWS (
FILTER (
'Lead',
'Lead'[Enquiry Source] <> "Verso Group"
&& 'Lead'[statecode] = 2
&& ('Lead'[new_disqualifiedreason] = 279640000
|| 'Lead'[new_disqualifiedreason] = 279640002
|| 'Lead'[new_disqualifiedreason] = 279640004)
)
)
)Disqualification Rate - Percent =
IF (
SELECTEDVALUE ( 'Lead'[Enquiry Source] ) = "Verso Group",
DIVIDE (
COUNTX (
FILTER (
'Lead',
( 'Lead'[new_disqualifiedreason] = 279640000
|| 'Lead'[new_disqualifiedreason] = 279640002
|| 'Lead'[new_disqualifiedreason] = 279640004 )
&& 'Lead'[Statecode] = 2
&& [Enquiry Source] = "Verso Group"
),
[Statecode]
),
COUNTROWS ( FILTER ( 'Lead', [Enquiry Source] = "Verso Group" ) )
),
DIVIDE (
COUNTX (
FILTER (
'Lead',
( 'Lead'[new_disqualifiedreason] = 279640000
|| 'Lead'[new_disqualifiedreason] = 279640002
|| 'Lead'[new_disqualifiedreason] = 279640004 )
&& 'Lead'[Statecode] = 2
&& [Enquiry Source] <> "Verso Group"
),
[Statecode]
),
COUNTROWS ( FILTER ( 'Lead', [Enquiry Source] <> "Verso Group" ) )
)
)Qualification Rate - Percent =
IF (
SELECTEDVALUE ( 'Lead'[Enquiry Source] ) = "Verso Group",
DIVIDE (
COUNTX (
FILTER (
'Lead',
'Lead'[Statecode] = 1
&& [Enquiry Source] = "Verso Group"
),
[Statecode]
),
COUNTROWS ( FILTER ( 'Lead', [Enquiry Source] = "Verso Group") )
),
DIVIDE (
COUNTX (
FILTER (
'Lead',
'Lead'[Statecode] = 1
&& [Enquiry Source] <> "Verso Group"
),
[Statecode]
),
COUNTROWS ( FILTER ( 'Lead', [Enquiry Source] <> "Verso Group" ) )
)
)Disqualified Reason =
IF (
SELECTEDVALUE ( 'Lead'[Enquiry Source] ) = "Verso Group",
COUNTROWS (
FILTER (
'Lead',
'Lead'[Enquiry Source] = "Verso Group"
&& 'Lead'[statecode] = 2
&& ('Lead'[new_disqualifiedreason] = 279640000
|| 'Lead'[new_disqualifiedreason] = 279640001
|| 'Lead'[new_disqualifiedreason] = 279640002
|| 'Lead'[new_disqualifiedreason] = 279640003
|| 'Lead'[new_disqualifiedreason] = 279640004)
)
),
COUNTROWS (
FILTER (
'Lead',
'Lead'[Enquiry Source] <> "Verso Group"
&& 'Lead'[statecode] = 2
&& ('Lead'[new_disqualifiedreason] = 279640000
|| 'Lead'[new_disqualifiedreason] = 279640001
|| 'Lead'[new_disqualifiedreason] = 279640002
|| 'Lead'[new_disqualifiedreason] = 279640003
|| 'Lead'[new_disqualifiedreason] = 279640004)
)
)
)That's all the measures that I created with the previous method but I want to add another value to them. My aim is to create a measure that shows the total leads, disqualified etc but excludes Facebook and Verso and have a button which shows the totals of either Facebook or Verso
If you have any questions please don't hesitate to ask
Thanks,
Mike
