Filtering Data Table without a relationship

Power BI Data Model
1

Is it possible to filter a data table, using DAX or some other method, if there is no relationship to desired look up table? Attached is a simple data model to illustrate what I am trying to accomplish.

The specific question is: Can I filter the financial aid table by student type even though the tables are not related?

Look Table Filtering.pdf (82.8 KB)

2

@eric_m,

Yes! As long as there’s a common field on which you COULD establish a relationship, you can use TREATAS to establish a virtual relationship to filter the table.

Here’s the link to the TREATAS entry in the eDNA Knowledge Base:
https://info.enterprisedna.co/dax-function-guide/treatas/

My experience is that it requires a bit more study than most functions to fully understand, but once you do, it will be one of the most useful tools in your DAX toolbox.

I hope this is helpful.

  • Brian
3

Thanks Brian. So in my example, there is no common field, so I cannot use Treatas?

4

@eric_m,

Is there a way you can create one from other fields in the dataset, perhaps through CONCATENATE? You will definitely need some way to tie the two tables together to do any sort of filtering, whether through a physical or virtual relationship.

If you can post your PBIX file, I’d be glad to take a look and see if there’s a way we can build the necessary info to apply TREATAS.

  • Brian
5

@eric_m,

OK, I took another look at the data model diagram and here’s how I think this can be done:

  1. either in Power Query through merge, add Student Type to the Financial Aid Table from the Tuition table, or do it via a calculated column using LOOKUPVALUE(). I think the first option is preferable, but either will work.

  2. once that’s done, you can use the following filter condition in a CALCULATE or CALCULATETABLE to create a virtual relationship between the Student Type lookup table in the Financial Aid data table:

    TREATAS(
    VALUES( ’ Student Type’[Student Type],
    ‘Financial Aid’[Student Type]
    )

The other option would be to merge your Financial Aid and Tuition tables into one that could be directly filtered via physical relationship by the Student Type lookup table.

I hope this is helpful.

– Brian

6

Thank you a Brian. I will add student type using PQ