PowerBi DAX — Conditional Measures to model relationships dynamically | by Maximilian Trekel

5 min readMay 28, 2021

PowerBi DAX — Conditional Measures to model relationships dynamically

When developing PowerBi reports, we have to work with relational Data out of the box most of the times. Our clients are often small to medium sized companies or companies who are just getting into the idea of centrally collecting and modelling data for analysis.

So the case is often that they are just trying out the process and the technology involved. What I am getting at is that there is no polished semantic model to draw data from and simply slap some tabular analysis on top to build some nice-looking visuals. Also, it is pretty time-consuming to go back and forth between PowerQuery and the actual PowerBi Desktop designer when building your datamodel so a nice clean on-the-spot ETL process is not an option either.

The result is mostly the same — what we would like to have, is a nice, snowflakey model with one fact table and the dimension table-families around them. With just all of our relationships being one-to-many (1:n) and active (so no circularity or ambiguity between tables). What we get is something a little bit more hands-on. Especially when working with Line of Business Applications as your data source.

We recently had the case where we had to model entities along the lines of the complete consulting-service business process depicted in one application. So tables reflecting things like: leads, sales-prospects or deals, accounts and their contacts, quotes, orders, projects and their respective budgets which are then assigned to resources which in the end book time entries against these budgets and oh — of course these resources are also found as system users — so meta…

This means, ambiguity in-and-out — account owners != project managers, people responsible for deals != resources on the projects resulting from the deals and so on and so forth. On and of course, lots and lots of dates: creation date, estimated closing date, budget start, budget end, date of service delivery, date of time entry booking. The list goes on.

Now what are the options when trying to build a consumable report in this scenario fast? Our approach was something I want to call dynamic modelling of relationships using DAX. I feel like there is already a lot of quality content around this topic. We are however encountering this particular use-case quite often these days and I never seem to find the messy realities in the tutorials out there, so here it goes:

We have to keep the family ties to a minimum. No extended members are added to our snowflake. We just call them when we need something from them. Sounds lovely right?

Lets say you want two filters on your report page. One person who runs the show. So in our case this person is a project manager, a deal captain, a bookable resource and maybe an account manager. You also want a time frame. Not three to four different date filter options, just a time frame in which things are, you know, happening. Maybe things happening this year (so a relative date filter). Lets look at the code first:

conditional_measure_date =IF ( 
    CALCULATE (
               COUNTROWS( TableToFilter),
                                        FILTER ( TableToFilter,
TableToFilter[DateColumnYouWantToReference]
> 
CALCULATE ( 
            MIN ( YourDateTable[YourDateColumn] ) 
)
>
0, 1, 0
)

So this looks weird. But this is because of me being to lazy to actually put the code on GitHub so I can format it and it being a pretty simple piece of functional code. You basically tell your measure to evaluate the table you want to filter and return 1 when a condition is met. Else it should return 0 (IF). This condition is that it is able to find rows (COUNTROWS) when being filtered (FILTER) against a specific date (MIN). In this case, we might look for the start date of a budget being after the smallest date in our general date table (which we are filtering relatively on our report page as one of our two filters).

Now, we just have to do one thing and that is to actually filter for the return value of our measure when it is evaluated in the context of a visual. We do this plain and simply by placing the maeasure in the filter pane under filters for this visual and set its value to 1:

I am hiding this pane in the end user report

You can of course also select any conditions you like as long as they can be evaluated when looking at the two tables you want to relate to one another. Lets look at our person or user-type filter before concluding:

conditional_measure_person =IF (
    CALCULATE (
               COUNTROWS( TableToFilter ),
FILTER ( TableToFilter,
                       TableToFilter[OwnerIdColumn]
= 
MIN ( TableUsedInPersonFilter[SameIdColumn] )
)
    ) , 1, 0
)

This second example looks a little bit weird because of the MIN-Function. We need this however, to be able to pull this table and its respective column into the row-based evaluation.

Of course there are some pitfalls with this as filtering for multiple values will not work (at least not for dimensions like users or product categories) and you will crowd your model with a lot of measures so be sure to organize them with a tidy measure table. However, when time is short and there is no budget or interest in a centralized semantic model (yet), you can try this out to create a user experience that does not question your end-users intelligence with by asking them to set a ton of filters per page.

Written by Maximilian Trekel