CALCULATETABLE to filter fact table using slicer to make multiple selections

[PBIuserNPA]

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

[lbendlin]

why would site locations control regional locations?

[PBIuserNPA]

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]

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.

[PBIuserNPA]

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 A

Table B

Merged Table

[PBIuserNPA]

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?

[lbendlin]

Sorry, can't work with screenshots.  Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[PBIuserNPA]

Use this link to access an Excel file containing three worksheets; Table A (fact table), Table B (Locations by Site) and Results (Table A and Table B merge on Location).

https://docs.google.com/spreadsheets/d/1cJKKg-1uWhMwR7Ijnf42UU92VbAPq-YK/edit?usp=sharing&ouid=11788...

[lbendlin]

Here are two options to consider:  (let me know which you prefer)

a) introducing a region disambiguation:

Location         Type            Site

UKUS	Global	Amarillo
UKUS	Global	Bristol
UKUS	Global	Dallas
UKUS	Global	Denver
UKUS	Global	Houston
UKUS	Global	Liverpool
UKUS	Global	London
UKUS	Global	Manchester
UKUS	Global	Oxford
Bristol, London or Oxford	SubRegion	Bristol
Bristol, London or Oxford	SubRegion	London
Bristol, London or Oxford	SubRegion	Oxford
Dallas or Houston	SubRegion	Dallas
Dallas or Houston	SubRegion	Houston
UK	Region	Bristol
UK	Region	Liverpool
UK	Region	London
UK	Region	Manchester
UK	Region	Oxford
US	Region	Amarillo
US	Region	Dallas
US	Region	Denver
US	Region	Houston
Amarillo	Site	Amarillo
Bristol	Site	Bristol
Dallas	Site	Dallas
Denver	Site	Denver
Houston	Site	Houston
Liverpool	Site	Liverpool
London	Site	London
Manchester	Site	Manchester
Oxford	Site	Oxford

b) introduce a hierarchy:

Location                              Type             Parent

UKUS	Global
Bristol, London or Oxford	SubRegion	UK
Dallas or Houston	SubRegion	US
UK	Region	UKUS
US	Region	UKUS
Amarillo	Site	US
Bristol	Site	Bristol, London or Oxford
Dallas	Site	Dallas or Houston
Denver	Site	US
Houston	Site	Dallas or Houston
Liverpool	Site	UK
London	Site	Bristol, London or Oxford
Manchester	Site	UK
Oxford	Site	Bristol, London or Oxford

[Pbiusrnpa]

Please elaborate. How would the first option be applied?

[lbendlin]

I thought about it some more, and I like the second option much more now.

You could even do clever filters based on PATHITEMS()

See attached.

[PBIuserNPA]

Thank you. But I prefer something like the pbix at the other end of the link below. It provides the intended result by using the slicer to select a site. The only problem, when no site is selected the table shows all curricula for all sites. Is there a way to default the table to show nothing and have the table show content only when a site is selected in the slicer?

https://drive.google.com/file/d/1RD_zg79m4zLY5M1zlPYJabZLP0Ot0653/view?usp=sharing

[lbendlin]

There is a way but it is undocumented.  There is a difference between "Nothing filtered"  and "Nothing filtered any more".  I don't think I want to lead you down that murky path.

[PBIuserNPA]

I added a record to Table A: Location = No Site

...and a record to Table B.

And I created a measure: 

Sum Display = CALCULATE(SUM('Results'[Display]))

and added it to the Table visual with the condition Sum Display > 0

The effect is, the Table visual shows no records when the Site = N/A is slected in the slicer.

Is it possible to default the Site slicer to Site = N/A and at the same time prevent the Site slicer from presenting the user with  Select All?

Links to source file and pbix file are provided.

https://docs.google.com/spreadsheets/d/1ZCeUXqmZKQnGmOC06-DSnWUcOhWGknEr/edit?usp=sharing&ouid=11788...

https://drive.google.com/file/d/1TSa0dMAR3sFiMXT-L9x4BmWgF0i14Bag/view?usp=sharing

[lbendlin]

 at the same time prevent the Site slicer from presenting the user with  Select All?

Sorry, I won't be able to help you with implementing a bad (in my opinion) user experience. Maybe someone else can assist.