Gutenvirt Tracker

Filter based on another column's value from an associated sclicer selection.

Gutenvirt — Wed, 31 May 2017 19:00:24 GMT

Hello,

I have a table of site names and site types. If a user selects one of the site names in a slicer, how I can show all other site names that have the same site type (based on the slicer selection)?

I.e.

Site_Name Site_Type

Site A 1

Site B 1

Site C 2

Site D 3

Site E 1

User selects 'Site A' and a table populates with 'Site A', 'Site B', and 'Site E'

Thanks!

Simple User Filtering options

Gutenvirt — Mon, 13 Feb 2017 15:35:20 GMT

I'm trying to filter data by the username of the current logged-in client. I realize there are solutions to this, RLS is enabled, but we don't have SSAS to manage the aggregations outside of PBI.

I created a measure that looks up a 'tag' value of the username() and a table that matches the username to a site location. Then I'm trying to create a column that calculates the max of the site number and filter on the measure's value.

CheckSite = IF(LOOKUPVALUE(UsersTab[Tag], UserTabs[Domain], USERNAME()), 1, 0)

CheckSecurity = CALCULATE(MAX(UsersTab[Site_Number]), FILTER(UsersTab, [CheckSite]= 1))

I was hoping to use the CheckSecurity column as a report-level filter, but I get an error that username() is not allowed in columns.

I'm trying to circumvent these restricts, but so far no luck.

Thanks!

Re: CALCULATE and filtering by aggregate comparison

Gutenvirt — Fri, 01 Apr 2016 12:07:20 GMT

Sorry, I should have provided a bit of context. I'm looking at an NCEA dataset about school districts. Here is a sample of the data:

district	rndstratio	num_schs	clsrm_tch_fte	num_stds
Alachua	16	72	1804	28157
Baker	16	9	312	4985
Bay	15	51	1794	27053
Bradford	13	12	243	3239
Brevard	15	128	4723	71232

My intent was to create a report that lists schools with a similar number of schools (+/- 15%) So if I select Alachua from a slicer, I would display the average number of students for all district that have between 62 and 83 schools.

I hope this helps; if the 'if' statements you mentioned might make this work. Thanks!

CALCULATE and filtering by aggregate comparison

Gutenvirt — Thu, 31 Mar 2016 16:16:06 GMT

I'm new to Power Bi and DAX but I have some familarity with Excel and Tableau.

In any event, I'd like to use the CALCULATE function to find the average of some column if the the average of another column is equal to the average of another column.

I tried:

InRange= CALCULATE(AVERAGE(tbl2[percent]),AVERAGE(tbl2[num_partic])=AVERAGE(tbl_input[num_build]))

It gives an error about True/False expressions (which I'd assume means it can equate two aggregate functions)

Ideally I'd have a predetermined tolerance of about +/- 15% but I can work on that if I get the above working. Any help woudl be greatly appreciated!