DAX measures help needed to help establish non equi join relationship

[gayatrikrishnan]

Hi,

I need to create a power bi dashboard that shows occupancy percentage at various levels using information related to parking sensor and parking rules. My requirement is to calculate occupancy details based on parking rules and filters applied by user while running the report. The dataset provided has relationships between each other based on three different fields out of which one of the criteria is a non equi join. In power bi, I am able to set relationship only on two columns that are equi join but missing relationship of third field is causing a many to many relationship. Following images include model and sample data.  

Parking Rule

Occupancy    

For a vehicle occupancy I need to find corresponding parking rule which is found by joining on day, rule id and event hour. For event hour I need to apply a between filter and to achieve that I am trying to create a measure to find if a particular parking time rule is applicable or not. I am using the below formula to filter out the record which to me seems to work but I am not sure if this is the best way to do this.

Time Included =
IF (
FIRSTNONBLANK ( 'Parking Lookup'[Time From], 1 ) <= MAX ( occupancy[Time] ) &&
FIRSTNONBLANK( 'Parking Lookup'[Time To], 1 ) >= MIN ( occupancy[Time] ) ,
"1",
"0"
)

The second measure that I need is to adjust the parking rule start and end time based on the user filter to calculate occupancy. For example if a rule is for time 9AM to 5PM and user is filtering for occupancy details from 9AM to 11AM, the total occupancy time has to be calculated for only the user filtered time. I have realized there are three main conditions as below

- Start Time can be less than user filtered time and user selected from time should be used

- End Time can be greater than user filtered time and user selected to time should be used

- All other instance where parking rule times are well within range doesn't need any modification

For the same I am trying to use the below measure which is not complete yet. 

Measure 1 = if(min('Parking Lookup'[24Hour]) < min('Time'[Time]),MIN('Time'[Time]),min('Parking Lookup'[24Hour]))

When I try to use both the measures together they are not working as expected and is breaking the relationship. I am completely lost where am I going wrong with this. I have a feeling the measures are disconnected hence the results are coming back as a cross join. There is certainly a better way to do this but I am unable to figure out. Any advise please. 

Thanks,

Gayatri

[Anonymous · Accepted solution]

Any relationship established on more than 1 field between 2 tables can be turned into a relationship involving only one column from each of the tables. On top of that, the so-called "many-to-many" relationship in PBI has a very clear-cut use-case. You should only ever use it when there's a granularity issue and never under any other circumstances.

So, what you should do is create a column in both tables that will correctly join them. You should also get rid of the 2-column relationships and turn them into 1-column relationships. If you work against the principles of PBI, you'll be creating issues all over the place and the model will be hard to control, not to mention DAX will become a nightmare to maintain. Do not do this. Work in harmony with the tool and your life will be easy and happy 🙂 Trust me, I know what I'm saying.

Here's how to turn an N-column relationship into 1-column. You just number consecutively the unique combinations of the keys from your N columns in one of the tables (the dimension one, that is) and then create a column in the other with the correct numbers that point to the right combination. That's all there is to it.

[gayatrikrishnan]

Thank you so much.

That was my 1st go to solution but this data is not loaded in our EDW and the reports are created on the fly I had to go down this path. It is such a pain to handle it in Power BI where one of the join is a between criteria. 

I think I should go back to my previous idea and may be add unique ids and create  a custom column using lookup formula in power bi itself. That should solve the whole problem. I am hoping the data transform steps should automatically work everytime when a new data comes in.

Appreciate your help. 

Thanks,

Gayatri