I have a fact table containing a list of unique Curricula (Curricula ID) and associated Location Type and Locations.I need to present this in a visual table and filter this visual with a slicer containing only the six sites. I found a way to do this by splitting the fact table of All Curricula into Site Curricula, Regional Curricula and Global Curricula.The method uses one-many and many-many relationships in a data model.I suspect there may be a way to filter the All Curricula table using the same Site Slicer and a CALCULATETABLE. But I don't know how to begin. Thank you

why would site locations control regional locations?

Hello Ibendlin. Thank you for asking. Regional Locations are a subset of Global Locations. And Site Locations are a subset of Regional Locations. And curricula are assigned to each of these location types as listed in the All Curricula table. What I want to do is use one slicer that lists only Site Locations to filter the All Curricula table. For example, if a user selects site Bristol the All Curricula table would filter to show only curricula associated with Bristol, i.e., C10, C16, C23, C4 (for site Bristol), C25, C6 (for region Bristol, London or Oxford), C2, C21 (for region UK), and C1 and C20 (for Global).

You will want to change your data model accordingly. The rolloup would have to be done with measures that collect the facts from the current and the downstream levels. Please provide sample data that fully covers your issue.Please show the expected outcome based on the sample data you provided.

My initial description may have been too cryptic. Please allow me another shot.I'm looking for a way to use a slicer to filter a fact table (Table A). The slicer will list Sites. The fact table lists locations and curricula IDs. These locations and associated sites are listed in a separate table (Table B). I can merge these two tables on the Location field. But I don't like the way this expands Table A. Is there a way to use DAX and an SELECTEDVALUE measure as a slicer to create tables on the fly and present them in a Table visual that can filtered by the Site slicer?Table ATable BMerged Table

With the merged table I can filter on the Site field to list Curricula IDs specific to a site. For example: Because the Table visual lists the content of this merged table, before a site is selected, the visual lists all 65 rows. I'd like to restrict this visual to show only the rows associated with the selected site.I can use the merged table, as is, if I can somehow set the visual to a default that lists nothing, and only lists a site's curricula when a site is selected. Is this possible?

CALCULATETABLE to filter fact table using slicer to make multiple selections

14 Replies

lbendlin
Super User
2 years ago
why would site locations control regional locations?
- PBIuserNPA
  Regular Visitor
  2 years ago
  Hello Ibendlin. Thank you for asking. Regional Locations are a subset of Global Locations. And Site Locations are a subset of Regional Locations. And curricula are assigned to each of these location types as listed in the All Curricula table. What I want to do is use one slicer that lists only Site Locations to filter the All Curricula table. For example, if a user selects site Bristol the All Curricula table would filter to show only curricula associated with Bristol, i.e., C10, C16, C23, C4 (for site Bristol), C25, C6 (for region Bristol, London or Oxford), C2, C21 (for region UK), and C1 and C20 (for Global).
  - lbendlin
    Super User
    2 years ago
    You will want to change your data model accordingly. The rolloup would have to be done with measures that collect the facts from the current and the downstream levels.
    
    Please provide sample data that fully covers your issue.
    Please show the expected outcome based on the sample data you provided.

Forum Discussion

CALCULATETABLE to filter fact table using slicer to make multiple selections