Calculate value based on slicer

[MiKeZZa]

I'm looking for a solution for this case:

A user must be able to see anything from any other department, but when the reported opens by default it must only show his own department(s).

It seems to be not possible to just selected 1 value in a slicer (with all the usernames), based on the user(principal)name. So my alternative workaround is to create a slicer called 'Show only my own department'. This one must open with default selection 'Yes' and can only contain 'No' as other value; 1 value must always be selected. For this I use Chicklet Slicer. So far no problems; but now I need to filter a measure based on this slicer.

Current situation:

Report Slicer: Name: 'Show only my department'. Possible values: Yes or No.

Dataset: Name: Table_ProductionNumbers. Columns: User_Email, SoldItems, CustomerID.

Now I want this behaviour:

When 'No' is selected I want to see the total sum of solditems and the distinct count of CustomerID. When 'Yes' is selected I only want to see the sum of solditems that have a value in User_Email that is equal to the value of USERPRINCIPALNAME() and for the distinct count of CustomerID I also want to count the CustomerID's of records where User_Email equals USERPRINCIPALNAME().

(How) Can this be done in DAX? And is there also a possible solution which does not need some DAX for every measure, but does actually 'filter' the dataset in Table_ProductionNumbers? This makes it a lot more easy to add some more measures later on...

[Greg_Deckler]

Likley would involve a measure calculation along the lines of:

IF(OnlyMyDept="Yes",calculate,calculate)

obviously psuedo-code, but that's the general idea.

[MiKeZZa]

I now have:

TestMeasure = IF(ShowOnlyMyDepartment="Yes",CALCULATE(COUNT(Table_ProductionNumbers[CustomerID])),CALCULATE(COUNT(Table_ProductionNumbers[CustomerID])))

I have 2 problems with this:

1. OnlyOwnDepartment is the name of the slicer, but it gives the error: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.". What's wrong here?
2. I now have the same count. But the second argument must be filtered. It must only COUNT the CustomerID's for customers where the field User_Email equals the userprincipalname. How can I do that?

[MiKeZZa]

I've now made these Measures:

1. CustCountFiltered
   

   CustCountFiltered = CALCULATE(DISTINCTCOUNT(Table_ProductionNumber[CustomerID]), FILTER(TableProductionNumber, TableProductionNumber[User_Email]="mail@adress.com"))

2. CustCountNotFiltered: 
   

   DistinctCount(Table_productionNumber[CustomerID])

3. CustCount

1. if(max(ShowOnlyDepartment_Slicer[OnlyOwnDepartment])="Yes",[CustCountFiltered],[CustCountNotFiltered])

This works well for now! The onliest thing is that I have to make something for the hardcoded mailadress. In PowerBI Desktop USERPRINCIPALNAME() doesn't work; the filtered values are empty then. But I'll give it a try after publishing.

This all works fine. So I'm happy now. But the onliest thing I'm interested now is if there is a possiblity to automatically filter the whole dataset. This because of that I now have to filter a table with CustCount > 0.

[v-yuezhe-msft]

Hi @MiKeZZa,

Could you please describe more details about automatically filtering the whole dataset? We will appreciate that if you can share sample data of your tables and post expected result here.

If you want to filter data with “CustCount>0”, just drag the measure to the visual level filter of your table visual and set the value of the measure to be greater than 0.

Thanks,
Lydia Zhang

[MiKeZZa]

Yes, I want exactly what you describe but then without placing a filter in the visual....

So I want every new added visual directly to lack the data that is filtered out in the visual with CustCount > 0 to prevent making mistakes by using the original measure's (I won't hide them)

[v-yuezhe-msft · Accepted solution]

---

@MiKeZZa wrote:

Yes, I want exactly what you describe but then without placing a filter in the visual....

So I want every new added visual directly to lack the data that is filtered out in the visual with CustCount > 0 to prevent making mistakes by using the original measure's (I won't hide them)

---

Hi @MiKeZZa,

The method I can think out is to drag the CustCount to "Report level Filters" and set the value of it to be greater than 0.  

Thanks,
Lydia Zhang