Link to home
Start Free TrialLog in
Avatar of H
H

asked on

SSAS Tabular - Dimension table

I have a dimension table in a tabular model that has been setup to only retrieve records that are in a fact table, by using the query editor and adding a where clause (where the dimension key is in the fact table). I've had a request to be able to view all the records in this dimension for a separate report. At the minute the Power BI reports using this dimension have good slicers with just the relevant records and not all the records in this dimension. My question is, what is the best way to be able to keep the slicer data trim according to the keys in the fact whilst also being able to create a report that would detail all of the records in this dimension, I'm just a little unsure of the best approach? I'm not sure whether to remove the where clause to bring all the records in and then filter the data out in the Power BI report or create a different solution.

Any help or ideas would be greatly appreciated. :)
Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image

So you want two dimension tables (in effect), one that only has dimensions that are in the fact table so slicers show only relevant dimensions.  The other dim table has all dimensions for reporting purposes, whether on the current fact table or not.  
Avatar of H
H

ASKER

Hi Tom,

Yes exactly :)

Thanks,
Helen
Hmmmm...  Is there a way to create a virtual dimension filtered table for fact matching and slicers leaving the other dimension table in tact?
Avatar of H

ASKER

I ended up creating a field in the dimension that flags whether a related dimension record exists in the fact table. I used the LOOKUPVALUE function and then an IF statement to evaluate if a blank() is returned then 0 or a 1 if it exists.

=IF(LOOKUPVALUE([ResultColumn],[ColumnToSearch],[ColumnUsedAsTheLookup]) = BLANK(), 0,1)

Avatar of H

ASKER

Thank you Tom for your comments :)
ASKER CERTIFIED SOLUTION
Avatar of H
H

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Smart solution, Helen..
Avatar of H

ASKER

Thank you. :)