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