Slicers from child table

[kalspiros]

Hi All!

So, the main dataset is [KBOSS Exports].

Each record is one project. It used to contain 60 columns, nearly 80% on null values that held information about the actual and budgeted hours worked for field and office works for all departments in the company. Most projects have values in just one department, thus the vast emptiness in all other columns.

I couldn't tolorate such a ridiculously wide and empty table thus the newly created KBOSS Departments that holds values like this:

As can be seen in Project ID 14836, yes, each project can have multiple departments.

The model looks like that:

Now even though i have extensively explained the paradoxical nature of using the [Department] as a slicer especially if used for counting records (a massacre!), i am still being asked to slice data which live in [KBOSS Export] per [Department].

For example, i have been asked to slice gross margins that lives in [KBOSS Exports] (i.e. one gross margin per Project, which makes sense) per [Department] which lives lower in my model.

As said, i have made absolutely clear that such a calculation will be a looming disaster, i.e. using a slicer from a child table to slice data to a parent table. But there we go... if we won't make it work, i'll have to reintroduce the 60 columns in [KBOSS Exports] and i'd prefer to scratch my eyes out than doing so.

Two + one solutions i'm currently thinking of and i'd like your input:

1. before "deporting" the 60 columns out from [KBOSS Exports], create a field in Power Query where it will stores in a multiple values' field all departments for each project. i.e. from the image above:

Project	Departments
14278	Monitoring
14836	Utility, Laser Scanning
15449	Monitoring

i can then create measures with use of COUNTROWS, FILTER, FIND etc.

2. look for a measure that will somehow do that for me? something like a reveresed RELATEDTABLE that will travel against the stream from MANY to ONE?

3. accept that my mere 3 months working with DAX are not enough for me to handle this or that i'm simply a bit thick and missing something pretty obvious to all of you!

How would you be handing that? Slicing parental data with child slicers?

Many thanks in advance!!

[kriscoupe · Accepted solution]

Hi @kalspiros,

Could you not just use the COUNTROWS function in a calculate with CROSSFILTER to change the direction of that relationship to both just for that calculation? Something like

CALCULATE(

    COUNTROWS( KBOSS Exports ),

    CROSSFILTER( <related column from exports>, <related column from departments >, BOTH )

)

Let me know if it helps

EDIT - You can of course replace the COUNTROWS( KBOSS Exports ) with any measure or calculation you need to perform on that table.

Kris

[kalspiros]

Hi Kris,

Interestingly enough, i did come across CROSSFILTER in the past but seems i failed to link my problem with this function!

It works perfectly fine, many thanks for your help!

Kind regards