Hey y'all, any help would be appreciated!
I have two core data tables, events and customers. There is a 'bridge' style table that links the two via foreign keys. It looks like this:
Premises 1-->* PremiseEvents * <--1 Events
The PremiseEvents table acts as a bridge table and only has three columns, PremiseID, EventID, and ID. ID is unique, PremiseID is a FK to Premises, and EventID is a FK to Events. All three tables are large, but as you can imagine the bridge table is huge (although it is narrow).
I'm trying to create a page where user can filter on several slicers tied to Events to get to a small number of events, say 2 or 3 or something. Once that filter is applied there would only be say 100 entries in PremiseEvents bridge table that links to the 100 customers that are impacted by that event.
If I drop a count(PremiseEventID) from the bridge table into the visual, it filters on both sides of the relationship and shows me what I want. But I'd prefer to not have that column on the table, and if I end up wanting to plot the some of this data that's not exactly a data point I want to plot. Putting a 'is not blank' filter on the same column on the visual does not work, I get errors saying the result set was more than 1M rows.
So I'm trying to create a measure or something that I can use to filter the visual. I'm having a hell of a time. Any other ideas or recommendations for an appropriate filter would be awesome.
Attempt 1:
CustFocus =
var __Tb = SUMMARIZE(
'UDM TableCustomerPremises'
,'UDM TableCustomerPremises'[ID]
)
var __PremFilt =
CALCULATETABLE(
__Tb
,CROSSFILTER('UDM TableEventPremise'[CustomerPremiseId],'UDM TableCustomerPremises'[ID],Both)
)
var __EventFilt =
CALCULATETABLE(
__PremFilt
,CROSSFILTER('UDM TableEventPremise'[EventListId],QualifiedEventList[ID],Both)
)
return
countrows(__EventFilt)
Attempt 2:
CALCULATE(
countrows(ALLSELECTED('UDM TableEventPremise') )
,CROSSFILTER('UDM TableCustomerPremises'[ID],'UDM TableEventPremise'[CustomerPremiseId],both)
)
