Apply same filter across multiple related tables (power pivot only)

[Anonymous]

Hi,

I'm new to the community but I have been looking at the forum quite a lot in the past couple of months... I usually manage to find what I'm looking for but unfortunately it seems this time I'm hitting a wall or not understanding the answers... And therefore I'm making the big jump and asking my question.

I have the following data:

And I have my relations set up as such in power pivot

I didn't have a problem setting up the 2 fist measures using USERELATIONSHIP:

Resource Logged time on Stream SAP line := 
calculate(
  sumx(TabSAPLog, TabSAPLog[TimeLogged]),
  userelationship(TabSAPLog[SAP Code], TabSAPCode[SAP Code])
)

Stream resource Logged time on SAP line := 
calculate(
  sumx(TabSAPLog, TabSAPLog[TimeLogged]),
  userelationship(TabSAPLog[Resource], TabResources[Resource]) //this one is superfluous but best be a bit more explicit
)

But the problem starts when trying to calculate

Stream Resource Logged time on Stream SAP line

i.e., when trying to filter on a value in the TabStream table to apply to both my TabSAPCode and TabResources tables to then propagate into TabSAPLog.

I have been trying with CROSSFILTER but no luck (maybe I'm doing it wrong)

Stream resource logged time on Stream SAP line:=calculate(
  sumx(TabSAPLog, TabSAPLog[TimeLogged]), 
  CROSSFILTER(TabSAPLog[Resource], TabResources[Resource],Both),
  CROSSFILTER(TabSAPLog[SAP Code], TabSAPCode[SAP Code],Both) 
) 

Similarly I'd also be interested in coming up with a measure doing some negative testing, i.e.,

Non Stream Resource Logged time on Stream SAP line
Stream Resource Logged time on Non-Stream SAP line

The idea is of course to provide a check that only resources attributed to a given stream book time on a stream specific SAP code, and only on those SAP code.

Many thanks for the help!

[Anonymous · Accepted solution]

I managed to find a workaround but it is not really scalable to more than 2 columns.

In Power Query, I basically:

• Create a new Column in TabSAPLog that has all the streams

#"Added Column TabStream" = Table.AddColumn(TabSAPLog, "Stream Tab", each TabStream)​
#"Expanded Stream Tab" = Table.ExpandTableColumn(#"Added Column TabStream", "Stream Tab", {"Stream", "Index"}, {"Stream Tab.Stream", "Stream Tab.Index"}),​

• Merge my way through TabResource and TabSAPCode to TabStream

#"Added Link to Resource" = Table.NestedJoin(#"Expanded Stream Tab", {"Resource"}, TabResource, {"Resource"}, "TabResource", JoinKind.LeftOuter),
#"Expanded TabResource" = Table.ExpandTableColumn(#"Added Link to Resource", "TabResource", {"Stream"}, {"Resource.Stream"}),
#"Added Link to SAP Code" = Table.NestedJoin(#"Expanded TabResource", {"SAP Code"}, TabSapCode, {"SAP Code"}, "TabSapCode", JoinKind.LeftOuter),
#"Expanded TabSapCode" = Table.ExpandTableColumn(#"Added Link to SAP Code", "TabSapCode", {"Stream"}, {"SAPCode.Stream"}),

• Build the conditional column, e.g., 

#"Added Both" = Table.AddColumn(
    #"Expanded TabSapCode", 
    "Stream.Index Both Resource and SAP Code", 
    each 
        if [Stream Tab.Stream] = [Resource.Stream] and [Stream Tab.Stream] = [SAPCode.Stream] then [Stream Tab.Index] 
        else null
),​

Then in Power Pivot I make the links back for each of the conditional columns highlighted bleow:

And then build my measures using USERELATIONSHIP:

Resource Logged time on Stream SAP line:=calculate(
  sumx('TabSAPLog Stream Link', 'TabSAPLog Stream Link'[TimeLogged]),
  USERELATIONSHIP(TabStream[Index], 'TabSAPLog Stream Link'[Stream.Index SAP Code inclusive])
)

Stream resource Logged time on SAP line:=calculate(
  sumx('TabSAPLog Stream Link', 'TabSAPLog Stream Link'[TimeLogged]),
  USERELATIONSHIP(TabStream[Index], 'TabSAPLog Stream Link'[Stream.Index Resource inclusive])
)

Stream resource logged time on Stream SAP line:=calculate(
  sumx('TabSAPLog Stream Link', 'TabSAPLog Stream Link'[TimeLogged]),
  USERELATIONSHIP(TabStream[Index], 'TabSAPLog Stream Link'[Stream.Index Both Resource and SAP Code])
)

Non Stream Resource Logged time on Stream SAP line:=[Resource Logged time on Stream SAP line] - [Stream resource logged time on Stream SAP line]

Stream Resource Logged time on Non-Stream SAP line:=[Stream resource Logged time on SAP line] - [Stream resource logged time on Stream SAP line]

Then it actually works

But as mentionned it's not particularly elegant...

PS: this is the full powerquery statement

let
    Source = TabSAPLog,
    #"Added Column TabStream" = Table.AddColumn(Source, "Stream Tab", each TabStream),
    #"Expanded Stream Tab" = Table.ExpandTableColumn(#"Added Column TabStream", "Stream Tab", {"Stream", "Index"}, {"Stream Tab.Stream", "Stream Tab.Index"}),
    #"Added Link to Resource" = Table.NestedJoin(#"Expanded Stream Tab", {"Resource"}, TabResource, {"Resource"}, "TabResource", JoinKind.LeftOuter),
    #"Expanded TabResource" = Table.ExpandTableColumn(#"Added Link to Resource", "TabResource", {"Stream"}, {"Resource.Stream"}),
    #"Added Link to SAP Code" = Table.NestedJoin(#"Expanded TabResource", {"SAP Code"}, TabSapCode, {"SAP Code"}, "TabSapCode", JoinKind.LeftOuter),
    #"Expanded TabSapCode" = Table.ExpandTableColumn(#"Added Link to SAP Code", "TabSapCode", {"Stream"}, {"SAPCode.Stream"}),
    #"Added Both" = Table.AddColumn(
        #"Expanded TabSapCode", 
        "Stream.Index Both Resource and SAP Code", 
        each 
            if [Stream Tab.Stream] = [Resource.Stream] and [Stream Tab.Stream] = [SAPCode.Stream] then [Stream Tab.Index] 
            else null
    ),
    #"Added Resource Only" = Table.AddColumn(
        #"Added Both", 
        "Stream.Index Resource only", 
        each 
            if [Stream Tab.Stream] = [Resource.Stream] and [Stream Tab.Stream] <> [SAPCode.Stream] then [Stream Tab.Index] 
            else null
    ),
    #"Added SAP Code Only" = Table.AddColumn(
        #"Added Resource Only", 
        "Stream.Index SAP Code only", 
        each 
            if [Stream Tab.Stream] <> [Resource.Stream] and [Stream Tab.Stream] = [SAPCode.Stream] then [Stream Tab.Index] 
            else null
    ),
    #"Neither Resource nor SAP Code" = Table.AddColumn(
        #"Added SAP Code Only", 
        "Stream.Index Neither Resource nor SAP Code", 
        each 
            if [Stream Tab.Stream] <> [Resource.Stream] and [Stream Tab.Stream] <> [SAPCode.Stream] then [Stream Tab.Index] 
            else null
    ),
    #"SAP Code" = Table.AddColumn(
        #"Neither Resource nor SAP Code", 
        "Stream.Index SAP Code inclusive", 
        each 
            if [Stream Tab.Stream] = [SAPCode.Stream] then [Stream Tab.Index] 
            else null
    ),
    #"Resource Code" = Table.AddColumn(
        #"SAP Code", 
        "Stream.Index Resource inclusive", 
        each 
            if [Stream Tab.Stream] = [Resource.Stream] then [Stream Tab.Index] 
            else null
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Resource Code",{"Stream Tab.Stream", "Resource.Stream", "SAPCode.Stream", "Stream Tab.Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Stream.Index Neither Resource nor SAP Code", type number}, {"Stream.Index SAP Code only", type number}, {"Stream.Index Resource only", type number}, {"Stream.Index Both Resource and SAP Code", type number}, {"Stream.Index SAP Code inclusive", type number}, {"Stream.Index Resource inclusive", type number}})
in
    #"Changed Type"

TabStream query is simply

let
    Source = Excel.CurrentWorkbook(),
    TabStream = Source{[Name="TabStream"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(TabStream,{{"Stream", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Stream"})
in
    #"Reordered Columns"

and same goes for TabSapCode, TabResource and TabSAPLog

[amitchandak]

@Anonymous , make one of the join less used stream to SAP code or Stream to Resource as inactive.  and use that.

or with help of merge in power query have stream populated in SAP log (Use on of smaller table Resource or  SAP code )

 the join directly with stream  and join stream to SAP code and  Stream to Resource should be inactive or not needed

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[Anonymous]

Hi @amitchandak 

Thank you for a very quick response.

Apologies but I am having troubles following your answer.

One of the joint (TabSAPCode to TabSAPLog) is already inactive because precisely the filter on TabStream cannot be followed for both which is precisely what I want to achieve, i.e., I want to have a measure that when a filter is applied on TabStream[Stream] filters both TabSAPCcode and TabResource (which it does) but that then this filtering is propagated down to TabSAPLog and that the only records in TabSAPLog be the ones for which both the SAP Code and the Resource are in the given stream.

I thought about copying the stream in TabSAPLog for both Resource and SAP Code but as you point out one of the link will be inactive and I will be in exactly the same situation no?

Basically I want 1 filter to apply on 2 columns of the same table at the same time.

Thanks again