Union table to itself with propagated filter

[EduardoCruz]

Hi all. This is my first time posting here. I'm new to power bi and dax, so bear with me.

I needed to union a table to itself when it is filtered. I need the filter from DimCompanySupplier[VAT] to propagate to InterestCategory[VAT] and union the result set from both filtered tables, all from the first input filter.

I am struggling to find the solution, I would really apreciate it if you could help me.

OpportunitiesByVAT =

VAR SelectedVat = SELECTEDVALUE(DimCompanySupplier[VAT];"ALL")

Return

UNION(

CALCULATETABLE(

FactActiveOpportunities; DimCompanySupplier[VAT] = SelectedVat

);

CALCULATETABLE(

FactActiveOpportunities; InterestCategory[VAT] = SelectedVat

)

)

Thanks in advance!

[v-juanli-msft · Accepted solution]

Hi @EduardoCruz 

Could you show an example data of the three tables and what relationships among them?

You could refer to my test first, if my data isn't the same as yours, please share an example as i do.

1.I use table1,2,3 to refer to your tables:DimCompanySupplier, FactActiveOpportunities, InterestCategory.

i also create relationships among three tables, please see details in my pbix.

2.i create a new table, note i don't create any relationship for this table with other tables.

new Table = VALUES(table1[vat])

3. create meausres in table2

Measure = SELECTEDVALUE('new Table'[vat])

Measure 1 = LOOKUPVALUE(table1[id1],table1[vat],[Measure])

Measure 2 = LOOKUPVALUE(table3[id3],table3[vat],[Measure])

Measure 4 = IF([Measure 2]=MAX(table2[id_3])||[Measure 1]=MAX(table2[id_1]),1,0)

4.add 'new Table'[vat] in the slicer, add Measure 4 in the visual level filter for that table visual,

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EduardoCruz]

Hi @v-juanli-msft 

I have encountered a similar difficulty, following my implementation of this solution.

One of the tables has a many to many relationship

For instance, each opportunity has a category assigned to it, I have a Category dimension, and I have a table with companies's category interests (so that they can be matched to the opportunities on which they might be interested, businesswise)

The thing is that the measure with the if statement only covers many to one, and returns an error if I try to get all the filtered values from the column I need.

For instance, in your example, table 3 could have for VAT A, id 7,8 and 9.

Is there any way to tackle this issue? I would post a picture of my model but I still don't have permissions.

Thanks in advance!

Eduardo

[EduardoCruz]

Thank you Maggie, this was exactly it. 🙂