Questions on 1) column and 2) table security and 3) grouping/printing totals.

[vnicholl]

H i,

Question 1: In a table I have put a field that has a word in it, based on a number in the table. For example, one for 1, two for 2. When I I attempt to create a column on my page, by saying if field = one, then display a total in the table, everything clears out. The field appears to be created in the table. I want to create it on the Power BI Desktop, but can't see where I could create that there from the create new column item. Any ideas?

Question 2: We are currently using Cognos Analytics. We have some reports that use the login you use to the system to give you access to certain table rows. So in other words you only see your table rows, not another depts. This is based off another table that lists the dept and user name. So when a person works with the Cognos report. while all the data is there, they are automatically restricted. How can we do this in Power BI?

Question 3: We have a report formatted like this with sums/totals in Cognos Analytics. How can I do the same thing in Power BI?

Dept 1     Group 1     Section 1     12

Dept 1     Group 1     Section 1     24

        Total for Dept 1, Group 1, Section 1 = 36

Total for Dept 1 = 36

Dept 2     Group 2     Section 1     10

        Total for Dept 2, Group 2, Section 1 = 10 

Dept 2     Group 2     Section 2     10

        Total for Dept 2, Group 2, Section 2 = 10

Total for Dept 2 = 20 

I want to have totals, like a line below each break between the section, group, and dept, to total those. 

Thanks. 

[vnicholl]

Question 3 hasn't been answered, I've answered 2 myself from what was given here (except for one issue) and #1 I might do differently.

[Anonymous]

Hi @vnicholl ,

For Question 3, I created a sample pbix file(see attachment), please check whether that is what you want.

Best Regards

[vnicholl]

That is part of it. In your example, I want to have each dept, group and section totaled, not just the overall totals. 

[lbendlin]

you will need to write separate measures if you want to sum items in a non-standard way. Also make sure your users are aware of the extra logic, to avoid confusion.

[vnicholl]

I can write the code, the problem is, how do I get it into the list? Would a matrix or a table be required to do this? 

[vnicholl]

Let me add more to this: https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls

So we have username for our specific item in a table. How do I find out whether or not the username listed in our table is what we can use and will work to restrict access to the 2nd table. 

[lbendlin]

You can use the DAX function USERPRINCIPALNAME() in your RLS rules for your affected tables.  That would represent the email address of the user.

You can also use different versions of RLS that work with distribution lists and permission maps.

[lbendlin]

Trying to make Power BI work like another system is called "fighting the API".  Embrace the difference.

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. 

[vnicholl]

I can use my example above of the dept group section.

I have a table that has a name, the levels they have access to
Jane Doe Dept 1 Group 1 Section 1
John Doe Dept 1 Group 1 Section 1
John Wayne Dept 2 Group 2 Section 1
Jane Wayne Dept 2 Group 2 Section 2
Joe Blow Dept 1 Group 1 Section 1
Joe Blow Dept 2 Group 2 Section 1
Joe Blow Dept 2 Group 2 Section 2

What I want to be able to do is to have my current report displaying the totals only available to the person who is running the report.

So if Jane Doe or John Doe logs in, they should only see the report having
Dept 1, Group 1, Section 1 totals, which is 36.
John Wayne logs in and should only be able to see Dept 2, Group 2, Section 1 totals which is 10
Jane Wayne logs in and should only be able to see Dept 2, Group 2, Section 2 totals which is 10
Joe Blow has access to everything and should see all totals: 36, 10 and 10.