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

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

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

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!

 

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors