How to implement hierarchy of slicers when 1 or more tables have inactive relationships.?

[chkstr]

Hi
I'm new to PBI.  I was able to quickly whip-up  up a solution  prototype (PBI is awesome)  that does what I want but was based on two very broad flat files. Now I want to scale up the solution to support millions of rows.   So, I broke out my original  flat file model  into a star model with 14 DIM tables and   2 Fact tables   (with huge performance gains).  

The Problem -  After converting data model  to Star pattern several of the Dims are now 'stranded' with Inactive Relationships. I've looked into using UseRelationship or creating virtual tables.  I'm sure this must be an issue with every substantive model so I must be missing something really obvious. 

Use Case 

My reports involve what I think of as Slicer Hierarchies e.g. The typical  instuction to the end user is
     Step One -Select the Workflow Type You want to view (from Slicer 1,  Dim table 1 , which has active relationships) 
     Step Two - Select the Task Type you want to analyze.  (from Slicer 2, Dim table 2 1 , has inactive relationship betrween Slicer 1 and Slicer 2) 

I have provided dropbox links with images and sample pbix that highlight the issue.
  1.  Image: Model of Data Subset
  2.  Image:  Basic Visualization Highlighting Desired Result
  3. PBIX with Data Subset 

https://www.dropbox.com/sh/mkig5aqypl39576/AABCRCi6dE_kT_xc9b7h81vga?dl=0

thx  ( and sorry to bore with such a newbie question) 

[harshnathani · Accepted solution]

Hi @chkstr ,

Create a measure.

Also try to make your model in a Star Schema else you may end up in trouble with other measures.

https://www.youtube.com/watch?v=-vTakVyIDUU

Mutual Measure = 
CALCULATE(COUNT('Tasks - Active WFs'[TASK_TYPE_ID]),USERELATIONSHIP('LookUp WF Types'[ID],'LookUp Task Types'[WF_TYPE_ID]))

Select Slicer 2 and put a Visal filter as shown below.

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[Ste_For94 · Accepted solution]

I think the problem is actually in your model: You can use userelationship to selectively activate a relationship when calculating a column a calculated table or a measure, but you cannot use it to filter data with a slicer

Your model is not a true star model:When you are not selectively activating a relationship with  userelationship the model does not know which filter direction it should use because the second dimension table can filter one of the two fact tables both directly and passing through the first dimension table

Hope I was helpful! if I did please kudos are always appreciate it and you can mark the post as a solution if I solved your problem

[chkstr]

Harsh, Ste_For94

You guys were right, I was totally barking up the wrong tree looking for a DAX solution when in fact the problem was with the model. I thought I had a star schema (kinda looked like a star) but after watching the suggested 

 https://www.youtube.com/watch?v=-vTakVyIDUU   

and following up with some more indepth material from  Russo and Ferrari @ SQLBI   I relaize that in fact I have a galaxy - snowflake hybrid.   And I thought I had done such a nice job of fully denormalizing my model - ( still think like a SQL guy).  So now its back to the drawing board but now I have a pretty road map and I'm pretty sure I can get to fully STAR  compliant model. 

Thx for the quick turnaround on this - has saved my a ton of time and  aggravation. 

[harshnathani · Accepted solution]

Hi @chkstr ,

Create a measure.

Also try to make your model in a Star Schema else you may end up in trouble with other measures.

https://www.youtube.com/watch?v=-vTakVyIDUU

Mutual Measure = 
CALCULATE(COUNT('Tasks - Active WFs'[TASK_TYPE_ID]),USERELATIONSHIP('LookUp WF Types'[ID],'LookUp Task Types'[WF_TYPE_ID]))

Select Slicer 2 and put a Visal filter as shown below.

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!