Forum Discussion
Distinc Count depending on filters
Hi all,
I'm stack in a formula and I hope you experts could helps me.
I have a table with 1m rows, there is a column, to identify the department, "DepName" which is always informed, then I have a report displaying information from users, that can belong to one or more departments.
I added a slicer to in order to give the chance to select only one department, so far, good. The problem comes when I try to use the department name as the header of the report, I'm trying to build a formula which displays "Multiple Departments" when there are more than one or [DepName] if there is only one selected.
I used a lot of contextual functions but I cannot get it.
Title = CALCULATE(DISTINCTCOUNT(FACT_F[DepName]),FILTERS(FACT_F[DepName]))
Report Title = IF(CALCULATE(DISTINCTCOUNT(FACT_F[DepName]),ALLSELECTED(FACT_F))>1,"MULTIPLE",FACT_F[DepName])
I would appreciate any help or advice, thank you in advance.
Cheers,
Guillermo
Well, its done by the measure:
Measure = IF (ISFILTERED([Column8]);IF(HASONEFILTER([Column8]);VALUES(Table[Column8]);"Multiple Departments");"Nothing selected")
Explaination:
if (some selection on slicer) then
if (num selections) == 1 then
return columnNameValueSelected
else (num selections >1)
return "Multiple Departments"
else
"Nothing selected"
Attach the measure into a Card and use it as Title.
Regards.
--
Javi
4 Replies
To check if one filter is active on a single column you can use HASONEFILTER() https://msdn.microsoft.com/en-us/library/gg492135.aspx
If I understood the question, you want some like:
IF (ISFILTERED([column]);IF(HASONEFILTER([column]);"One Department";"Multiple Departments");"do some")
Hi,
thanks for your input, but I think it's not the case
What I ment is I need to count how many departments the query have retrieved and then if there is more than one display "Multiple" or display the department name column [DepName] if there is only one.
But in the case when there are more than one and the users filter it using ans slicer the filed should be updated and display the single value.
I don't know if I explained well.
Cheers
Well, its done by the measure:
Measure = IF (ISFILTERED([Column8]);IF(HASONEFILTER([Column8]);VALUES(Table[Column8]);"Multiple Departments");"Nothing selected")
Explaination:
if (some selection on slicer) then
if (num selections) == 1 then
return columnNameValueSelected
else (num selections >1)
return "Multiple Departments"
else
"Nothing selected"
Attach the measure into a Card and use it as Title.
Regards.
--
Javi