Distinc Count depending on filters

[gbarrachina]

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

[javi0unavailabl]

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")

[gbarrachina]

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

[javi0unavailabl · Accepted solution]

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

[gbarrachina]

It worked, thank you very much!