Date granularity reduced due to Data Model route. How to fix?

[LEMRAK]

Hi All,

In the Modeling table, there are modeled values versus ModelDate. the ModelDate is random and doesn't follow any pattern.

In the Measurement table, there are measurement values for each day; thus, there is one value for each day.

My Tables are linked with two bridge tables, one for dates and one for the measureID. 

I use CROSSFILTER function to get the related measurement of the modeled value.

however, when plotting the values in line charts, I got the date granularity of the ModelDate. so not all measured values are represented! for the X axis I'm useing the date from the Calendar[date] (the bridge)

How to alter that so I can see all the measured values (daily). and still, see the Modeled values whenever there is modeled value?

the desired output for measured values is:

Appreciate your help as I've tried many things and failed to solve it.

[mahenkj2]

Hi @LEMRAK 

Can you please share.pbix file after removing sensitive data.

[LEMRAK]

I found that the problem is due to a slicer on the MODELING table. when the slicer is cleared out, the date granularity is working fine.

I thought changing the relation direction between the MODELING Table and the bridge to dual-directional would solve the problem. but it didn't.

I need to use that slicer and it needs to be from the Modeling table.

[Ashish_Mathur]

Hi,

Would you be OK with this approach?

1. Using the Query Editor, we merge data from the Modelling table into the Measurement table to bring over the Pipe column
2. We create 2 new Dimension tables - one for Pipe ID and another for Point (which is the MeasureID column in the Modelling table) and create relationships

 With these changes, we can get your desired result.  Would you be OK with this approach?

[LEMRAK]

Thanks for responding. this is an interesting approach.
However, each table has >5M rows. any data joining in the Query editor will dramatically increase the refresh time. so I'd avoid that.