Filter Both Table if value exists in one and other fact table

[glizzzd]

Hi,

I have two fact tables A & B. From A I am pulling data on cost, from B budgets. For both I created calculated column which is a combination of three columns. I also created another table C joining these with one to many relationship. The goal is to filter table A and B, using table C. If value exists in a table A & B i want to call this filter "Including", if not "Excluding".

Table A:

cost center	account	entity	cost ($)	calculated_col
A	1	X	10	A1X
B	2	Y	20	B2Y
C	3	Z	30	C3Z

Table B:

cost center	account	entity	cost ($)	calculated_col
A	1	X	10	A1X
B	2	Y	20	B2Y
C	3	Z	30	C3Z

Table C:

A1X

CVY

C3Z

My current solution is I set up filter to be calculated column from table A, set bidirectional filtering to table C and then from C to A single filtering. But the filter works only if i select and deselect and select one more time.

[v-heq-msft · Accepted solution]

Hi @glizzzd ,
Based on your description, I created the following dax expression and applied it to this visual object

Measure = 
IF(
    SELECTEDVALUE('Table A'[calculated_col]) IN VALUES('Table C'[Filter]),
    "Including",
    "Excluding"
    )

When we set the filter condition to "Including", the visual object will keep all the data with "Including" returned by the measue condition, and these filtered data can still be used by other filters afterward. filtered data can still be used by other filters later. In the end, the visual object will only keep the data that matches all the filters.

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[glizzzd]

Filter on the visual:

Filter = IF(ISBLANK(RELATED('Table C'[xxxx]))=TRUE(),"Excluding","Including")

Budget_USD_cal = CALCULATE(sum('aaa'[jksx]),ACCOUNT[vvvv]<>"BC",ALLSELECTED('SSSS'[xxxxx]))

If i put filter lets say to cost center and i have previously set up filter to Exclude it is not showing the values in a visual. However if i deselect Excluding and select it one more time it shows the values. What could be an issue here?