cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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:

ggallet_0-1597909727361.png

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

ggallet_1-1597909841835.png

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!

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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:

ggallet_0-1598177678228.png

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

ggallet_1-1598178282460.png

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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:

ggallet_0-1598177678228.png

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

ggallet_1-1598178282460.png

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
Super User
Super User

@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

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
Anonymous
Not applicable

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?

ggallet_1-1597934440247.png

 

ggallet_0-1597934403522.png

 

CDViewer_c9JdODsWZg.png

 

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

 

Thanks again

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!