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

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

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) 

2 ACCEPTED SOLUTIONS
harshnathani
Super User III
Super User III

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.

 

1.jpg

 

 

 

 

2.JPG

 

 

 

3.JPG

 

 

Regards,
Harsh Nathani

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

 

 

View solution in original post

Ste_For94
Frequent Visitor

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

View solution in original post

3 REPLIES 3
Ste_For94
Frequent Visitor

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

View solution in original post

chkstr
Frequent Visitor


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

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.

 

1.jpg

 

 

 

 

2.JPG

 

 

 

3.JPG

 

 

Regards,
Harsh Nathani

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

 

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates