Creating a subset of table to isolate ID then using those ID values to filter the original fact tabl

[Anonymous]

Hello community (clarification),

I need help with identifying all unit_ids in my fact table (picture 3) that meet my slicer selection criteria. Then I want to clear all selections on that fact table and filter that fact table on those unit_ids that I selected in my previous step. I can accomplish this with two fact tables but because the fact table is huge (and will grow fast) I would prefer to solve this with DAX measures and not with redundant data if it is possible (picture 1). I don't mind duplicating the any table but the units table.

My question is, is it possible? If yes, how do I do it? Data and screenshot are provided below.

Picture 1 - The mechanism 

Description: The three slicers at the top of Step 1 filter the table below them. That is what normally happens. That is not the reslut I want, I want the result in Step 2 where measures somehow magically identify the UnitIds and filters a table based on that.

Picture 2 - Data model

Picture 3 - units table, the fact table

units table data

UnitsId	Dates	CategoryId	SubCategoryId
1	20210101	1	1
2	20210101	1	2
3	20210101	1	2
1	20210101	2	3
2	20210101	2	3
3	20210101	2	4
1	20210101	3	5
3	20210101	3	6
2	20210101	3	6
3	20210101	4	7
1	20210101	4	7
2	20210101	4	7
1	20210101	5	8
3	20210101	5	9
2	20210101	5	9

Picture 4 - dates table

dates table data

DateId	Date	Year	Month	Day
20210101	2021-01-01	2021	1	1
20210102	2021-01-02	2021	1	2
20210103	2021-01-03	2021	1	3
20210104	2021-01-04	2021	1	4
20210105	2021-01-05	2021	1	5
20210106	2021-01-06	2021	1	6
20210107	2021-01-07	2021	1	7
20210108	2021-01-08	2021	1	8
20210109	2021-01-09	2021	1	9
20210110	2021-01-10	2021	1	10

Picture 5 - categories table

category table data

CategoryId	Category
1	A
2	B
3	C
4	D
5	E

Picture 6 - subcategories table

subcategory table data

SubCategoryId	CategoryId
1	aa
2	ab
3	ba
4	bb
5	ca
6	cd
7	da
8	ea
9	ee

OLD MESSAGE BELOW

I have a challenge I have not been able to solve with DAX. I have 5 tables in my data model: four dimension tables and one fact table. The fact table has five columns, one id column and four columns which correspond to the four dimension tables.

In the first step I would like to select a subset of the rows from the fact table. I do this with the help slicers based of the dimension tables. So far so good. From now on, it gets difficult. With the current selections made I would like to “keep” the id values of that subset (selection).

Now with those id values isolated I want to reset all filters on the fact table and use those ids to create the actual data subset of interest, i.e. I want to use those ids to filter the original and unfiltered fact table. I then want to use distinctcount to count the occurrences of those ids across the dimensions.

READ UPDATE BELOW

A mocup of my fact table:

id	cat_a	cat_b	cat_c	date
1	a	aa	aaa	20210101
1	a	ab	aba	20210101
1	a	ab	abb	20210101
1	b	ba	baa	20210101
1	b	bb	bba	20210101
2	a	aa	aaa	20210101

NOTE: I have been able to solve this with duplicating my fact table, but since this table is a couple of millions of rows and is growing fast I would like to avoid the duplication of the fact table and solve it with DAX and maybe duplicates of the dimensions if necessary.

Thank you for reading.

UPDATE

This problem has me so confused I included the wrong mockup. I'm sorry! This is how the fact table looks:

Id	Date	Cat	SubCat
1	20210101	cat_a	aa
2	20210101	cat_a	ab
3	20210101	cat_a	ab
1	20210101	cat_b	ba
2	20210101	cat_b	ba
3	20210101	cat_b	bb
1	20210101	cat_c	ca
3	20210101	cat_c	cd
2	20210101	cat_c	cd
3	20210101	cat_d	da
1	20210101	cat_d	da
2	20210101	cat_d	da
1	20210101	cat_e	ea
3	20210101	cat_e	ee
2	20210101	cat_e	ee

[Anonymous]

2nd attempt to get help with this challenge. Does anyone have any idea how to go about it?

[Anonymous]

Bump (only once) b/c rewritten message with screenshots and data.

[amitchandak]

@Anonymous , You try something like this , but usually remove filter from context does not remove filtered value

measure =

var _1 = summarize(allselected(Table), Table[ID])

return

calculate([Meausre], filter(all(Table), Table[ID] in _1) )

But this may not show dimesion values which are not selected in slicer

[Anonymous]

Hello @amitchandak ,

Thank you for the quick reply. Unfortunately the suggested measure does not work as expected. The measure repeats the total value on every row in the table visual. The table visual only contains cat_a, cat_b, cat_c and the measure.