Gaps in Relationship Data - Help

[ExcelMonke]

Hello,

I am having an issue with a relationship between two tables which is based off of a rounded value. I am not quite sure how to fix this or create a work-around.
What I am trying to achieve:
I have a table which calculates the average number of days per week a location is open on a 3 month basis, rounded to 1 decimal figure. The DAX for this is:

DaysOpenPerWeek = 
VAR _DaysWeek
VAR _Weeks
Return
ROUND((_DaysWeek/_Weeks),1)

I have a table that puts the result from [DaysOpenPerWeek] into buckets. The Table is created with the following DAX:

Day Bucket = GENERATESERIES(0,7,0.1)

 This creates a column [value]. My bucket column is a switch function as follows:

Bucket = 
SWITCH(
    TRUE(),
    'Day Bucket'[Value]<=0.4,"<0.5",
    'Day Bucket'[Value]>0.4 &&'Day Bucket'[Value]<=1.5,"0.6 - 1.5",
    'Day Bucket'[Value]>1.5&&'Day Bucket'[Value]<=2.5,"1.6 - 2.5",
    'Day Bucket'[Value]>2.5&&'Day Bucket'[Value]<=3.5,"2.6 - 3.5",
    'Day Bucket'[Value]>3.5&&'Day Bucket'[Value]<=4.5,"3.6 - 4.5",
    'Day Bucket'[Value]>4.5&&'Day Bucket'[Value]<=5.5,"4.6 - 5.5",
    'Day Bucket'[Value]>5.5&&'Day Bucket'[Value]<=6.5,"5.6 - 6.5",
    'Day Bucket'[Value]>6.5&&'Day Bucket'[Value]<=7,"6.6 - 7"
)

I am connecting the Fact Table (table with Number of Open Days) to the bucket table with Bucket[Value] 1:* relationship to FactTable[DaysOpen]. The problem is that this works inconsistently and is able to link some, but not all values. This can be seen here:

And visualised here

What I have tried:

I have various work-around by removing rounding, adding in a format clause, as well as creating specific mapping columns with formatting/rounding clauses to make sure the relationship is cleaner, and finally having the switch function look at the direct value rather than a value in between. Nothing has been working and I am quite lost on why it isn't. There are no page level filters that are impacting this result. Any help is appreciated!

[ExcelMonke]

Thanks again for the input everyone. I eventually created a "helper" column to better map the values to the buckets and this seemed to work.

[Anonymous]

Hi, @ExcelMonke 

Glad to hear you solved the problem yourself!  
If you can, please share your solution, which will be beneficial for future users to read this post. 

Best Regards

Yongkang Hua

[Anonymous]

Hi, @ExcelMonke 

Adjust the bucket logic to account for the precision of the calculations. Given the accuracy issue, you may need to expand the conditions in the function a bit to ensure that all possible values fall within a particular bucket.
If the direct relationship is still an issue, consider creating an intermediate table that explicitly maps values to buckets. You can then use this table to establish a clean relationship between the fact table and the bucket table.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[ExcelMonke]

Thank you for the input. The "bucket" table is actually a standalone table, where the Days Open is a calculated column based on a couple of calculations in the fact table. Will the value-in-between (Value >X && <=Y)clause not capture these results?

[lbendlin]

No.  Calculated Columns and Calculated Tables are calculated only once, during the Semantic Model refresh. They do not react to user filters.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...