cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Slicers from child table

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:

2020-06-28 11_27_56-MSL Project Review-V4 - Power BI Desktop.png

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

 

The model looks like that:

2020-06-28 11_08_07-MSL Project Review-V4 - Power BI Desktop.png

 

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:

ProjectDepartments
14278Monitoring
14836Utility, Laser Scanning
15449Monitoring

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!!

1 ACCEPTED SOLUTION
Resolver IV
Resolver IV

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

View solution in original post

2 REPLIES 2
Resolver IV
Resolver IV

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

View solution in original post

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors