Need to create calculated measure with IF Statement for Dimension Column

[PUTTAMADHU]

Hi All,

I have a requirement as follows.

I have list of users (Eg: A,B,C) available in excel file and I am loading this file in report.

I am creating a table chart with some columns. In that table there is one column where the logic is as follows.

If user access the report is part of my excel list users then display that respective column values in the chart table else display "Confidential".

Eg :

User D login's (User Prinical Name) the report. In that chart table, for that column it should show "Confidential" since he is not part of the list of users which is coming from excel file.

User C login's  (User Prinical Name) the report. In that chart table, for that column it should show "respective values of that column" since he is part of the list of users which is coming from excel file.

DAX I wrote for that column : Calculated Measure since I am using UserPrincipalName()

If (UserPrincipalName() = (List of users from excel) , Country, "Confidential") --> This is throwing error since that Country column is not aggregated with any function.

Any work around, please suggest. Thanks.

[PUTTAMADHU]

Table A: Sample Data as follows

EmpID	Country	Sales
1	IN	10
1	USA	20
2	IN	30
2	UK	40
3	USA	50

Table B: Sample User List Table

UserID

A

B

C

Now I created a PBIX File and brought above two tables in to my model.

And created one Table Chart in Report View.

Chart Table: I need to create a chart table with logic as follows.

First Case: 

One user eg D (UserPrincipalName) open the report, he should get the output of the chart table as follows.

EmpID	New Country
1	Confidential
2	Confidential
3	Confidential

Because he (User D) is not part of my user list (Table B)

Second Case:

One user eg C (UserPrincipalName) open the report, he should get the output of the chart table as follows.

EmpID	New Country
1	IN
1	USA
2	IN
2	UK
3	USA

Since user D is part of my Table B, I need to show the country column values in chart table. 

Hope this helps to understand my question.

Because he (User D) is not part of my user list (Table B)

[Thennarasu_R]

Hi @PUTTAMADHU 
we need to use an IF Condition in Measure .Any one function should use in measure like Slectedvalue or Max so You not mention in if condition.
Try this one

Measure=If ( MAX(List of users from excel) =UserPrincipalName(List of users from excel) , Country, "Confidential") .

If have helpful to you Give kudos and Accepted a Solution.


Thanks,
Thennarasu

[PUTTAMADHU]

Hi ,

Can you look into my reply with some sample data and let me know whether can we achieve it. Thanks.

[Greg_Deckler]

@PUTTAMADHU Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.