Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
roelf
Helper II
Helper II

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
v-yulgu-msft
Employee
Employee

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.
Anonymous
Not applicable

@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.

Thanks @Anonymous - 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!

Anonymous
Not applicable

@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.

 

 

@Anonymous

 

 

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

Anonymous
Not applicable

@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.

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.