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

Slice from FACT or RELATIONAL table

Hi

 

We use PBI Embedded and are trying to optimize the capacity usage.

 

I have a fact table with : Date | Template ID | Values

 

I have a relational table with : ID | Label | Template ID

 

I want to show these in a table :

Date | Label | Values

 

And I want to use a slicer for "Label"

 

I can do this in the following way:

Do a "LOOKUPVALUE" and change the fact table to;

Date | Template ID | Values | Label   and then slice from the Label in the fact table

 

OR

 

Slice directly from the Related table.

 

My data structure is a bit more complex than in this example, but my question is, as a rule of thumb, is it better to perform a lookup and slice from the FACT table, or is it better to slice from a related table ?

 

Thanks

 

7 REPLIES 7
DanBusIntel Member
Member

Re: Slice from FACT or RELATIONAL table

@roelf

 

I'm not sure I quite follow your question but...

 

As 'a rule of thumb' you would want to filter (slice) from the dimension tables (what I think you are referring to a related tables). If the data model is set up correctly you would have single direction filtering going from the DIM tables to the FACT table. Power BI allows bi-directional filtering but other products in the Power series only allow single directional filtering.

 

I think you may want to arrange your model a little better, but I've given you a rule of thumb.

roelf Regular Visitor
Regular Visitor

Re: Slice from FACT or RELATIONAL table

Thanks @DanBusIntel - that helps a lot.

 

If i want to use a value from a DIM table in the fact table to do a calc - is it then better to do a LOOKUP for the specific values to use in the calc, and add a column for it (It feels less capacity intensive) or should I reference the DIM table's value?

 

Also - I use RLS to enable different customers reading the same PBI file. I user the USERNAME() to filter the fact table. With single direction relationship it means that I get unwanted values in the DIM filter. In this instance, would you recommend using a USERNAME() filter on the DIM table also, or rather use Bi Directional relationships ?

 

Thanks again!

DanBusIntel Member
Member

Re: Slice from FACT or RELATIONAL table

@roelf

 

"use a value from a DIM table in the fact table to do a calc" - I'm afraid I don't follow. Is your value in the DIM table or FACT table?

Typically in a start schema approach you will only have references in a FACT table that reference. Each reference value will direct to it's associated DIM table. The remaining content in a FACT table will be values you use for calculations. So by default you would use the DIM value. Again, I don't understand you question fully and cannot see your model so I'm just explaining the basics as a result.

 

Reading the second bit of your question, I echo the data model aspect. Have a read online at modelling data and I'll think you'll end up answering your own question. If you're not going down the full star schema approach then it really is specific to your model and we need visual on that to be able to help.

 

 

roelf Regular Visitor
Regular Visitor

Re: Slice from FACT or RELATIONAL table

@DanBusIntel

 

 

Thanks again for your feedback - much appreciated.

 

I am happy to show you the data (Just not sure how much you are willing to look at - I can paste a screen shot of the model here if that will tell you something)

 

I am struggling to decide when to "clutter" the fact table with values using LOOKUPVALUE vs performing calculated columns by referencing data from the DIM tables.

 

 

Are there any particular online resources you can recommend ?

 

Thanks

roelf Regular Visitor
Regular Visitor

Re: Slice from FACT or RELATIONAL table

I think i have the incorrect table as the FACT Table. I will re-work my data model...

DanBusIntel Member
Member

Re: Slice from FACT or RELATIONAL table

@roelf

 

You can post your data on here and explicit details on what you're trying to achieve but it may be a while before I can take a proper look at it.

 

Using USELOOKUPVALUE tells me a few things about the structure of your data. Post all and I (or others) can help.

Community Support Team
Community Support Team

Re: Slice from FACT or RELATIONAL table

Hi @roelf,

 

Per my knowledge, if there existing a relationship between two tables, using slice from a related table looks like easiler and more direct. But sometimes relationship cannot be established between 2 tables, LOOKUPVALUE is inevitable.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.