Re: line chart visual bug

lolxd · ‎09-25-2023

Hi,

encountered this weird visual bug (upper left visual), where one `x` value seemingly maps to many `y` values, which it shouldnt, obviously.

For context, i am trying to calculate the count of BOs from `fact_table_range` that have the attribute `DATE_FROM` lower than `DATE` and `DATE_TO` greater than `DATE` for each `DATE` from `calendar_daily`. So basically ```

select * from calendar_daily inner join fact_table_range on calendar_daily.date >= DATE_FROM and (DATE_TO is null or calendar_daily.date <= DATE_TO)

``` Since i could not find a way to do this in power bi directly, I am using `dimension_table_range` which "translates" given date to all the possible `COMPRESSED_RANGE`s that given date belongs to. (`COMPRESSED_RANGE` is just `DATE_FROM` and `DATE_TO` concatenated into a string so that i can specify a relation between `dimension_table_range` and `fact_table_range` using single `COMPRESSED_RANGE` attribute rather than `DATE_FROM` and `DATE_TO` attributes)

Also, the dataset i am running the visualization on is a rather big dataset, where each of the dates matches up to a million rows.

Any help would be appreciated

<table class="table table-bordered table-hover table-condensed">
<thead><tr><th title="Field #1">DATE_FROM</th>
<th title="Field #2">DATE_TO</th>
<th title="Field #3">DATE</th>
<th title="Field #4">COMPRESSED_RANGE</th>
</tr></thead>
<tbody><tr>
<td>01.01.2020</td>
<td> </td>
<td>01.01.2020</td>
<td>2020-01-01:null</td>
</tr>
<tr>
<td>01.01.2020</td>
<td> </td>
<td>02.01.2020</td>
<td>2020-01-01:null</td>
</tr>
<tr>
<td>01.01.2020</td>
<td> </td>
<td>03.01.2020</td>
<td>2020-01-01:null</td>
</tr>
<tr>
<td>01.01.2020</td>
<td> </td>
<td>04.01.2020</td>
<td>2020-01-01:null</td>
</tr>
<tr>
<td>01.01.2020</td>
<td> </td>
<td>05.01.2020</td>
<td>2020-01-01:null</td>
</tr>
<tr>
<td>02.01.2020</td>
<td> </td>
<td>02.01.2020</td>
<td>2020-01-02:null</td>
</tr>
<tr>
<td>02.01.2020</td>
<td> </td>
<td>03.01.2020</td>
<td>2020-01-02:null</td>
</tr>
<tr>
<td>02.01.2020</td>
<td> </td>
<td>04.01.2020</td>
<td>2020-01-02:null</td>
</tr>
<tr>
<td>02.01.2020</td>
<td> </td>
<td>05.01.2020</td>
<td>2020-01-02:null</td>
</tr>
<tr>
<td>02.01.2020</td>
<td>02.01.2020</td>
<td>02.01.2020</td>
<td>2020-01-02:2020-01-02</td>
</tr>
<tr>
<td>03.01.2020</td>
<td>03.01.2020</td>
<td>03.01.2020</td>
<td>2020-01-03:2020-01-03</td>
</tr>
<tr>
<td>04.01.2020</td>
<td> </td>
<td>04.01.2020</td>
<td>2020-01-04:null</td>
</tr>
<tr>
<td>04.01.2020</td>
<td> </td>
<td>05.01.2020</td>
<td>2020-01-04:null</td>
</tr>
</tbody></table>

<table class="table table-bordered table-hover table-condensed">
<thead><tr><th title="Field #1">BO_ID</th>
<th title="Field #2">ATTR_0</th>
<th title="Field #3">DATE_FROM</th>
<th title="Field #4">DATE_TO</th>
<th title="Field #5">COMPRESSED_RANGE</th>
</tr></thead>
<tbody><tr>
<td align="right">0</td>
<td>C</td>
<td>02.01.2020</td>
<td>02.01.2020</td>
<td>2020-01-02:2020-01-02</td>
</tr>
<tr>
<td align="right">0</td>
<td>I</td>
<td>03.01.2020</td>
<td>03.01.2020</td>
<td>2020-01-03:2020-01-03</td>
</tr>
<tr>
<td align="right">0</td>
<td>M</td>
<td>04.01.2020</td>
<td> </td>
<td>2020-01-04:null</td>
</tr>
<tr>
<td align="right">1</td>
<td>Z</td>
<td>02.01.2020</td>
<td> </td>
<td>2020-01-02:null</td>
</tr>
<tr>
<td align="right">4</td>
<td>N</td>
<td>06.01.2020</td>
<td> </td>
<td>2020-01-06:null</td>
</tr>
<tr>
<td align="right">5</td>
<td>C</td>
<td>01.01.2020</td>
<td> </td>
<td>2020-01-01:null</td>
</tr>
<tr>
<td align="right">7</td>
<td>U</td>
<td>01.01.2020</td>
<td> </td>
<td>2020-01-01:null</td>
</tr>
</tbody></table>

lbendlin · ‎09-29-2023

DATE_FROM	DATE_TO	DATE	COMPRESSED_RANGE
01.01.2020		01.01.2020	2020-01-01:null
01.01.2020		02.01.2020	2020-01-01:null
01.01.2020		03.01.2020	2020-01-01:null
01.01.2020		04.01.2020	2020-01-01:null
01.01.2020		05.01.2020	2020-01-01:null
02.01.2020		02.01.2020	2020-01-02:null
02.01.2020		03.01.2020	2020-01-02:null
02.01.2020		04.01.2020	2020-01-02:null
02.01.2020		05.01.2020	2020-01-02:null
02.01.2020	02.01.2020	02.01.2020	2020-01-02:2020-01-02
03.01.2020	03.01.2020	03.01.2020	2020-01-03:2020-01-03
04.01.2020		04.01.2020	2020-01-04:null
04.01.2020		05.01.2020	2020-01-04:null

date
01.01.2020
02.01.2020
03.01.2020
04.01.2020

BO_ID	ATTR_0	DATE_FROM	DATE_TO	COMPRESSED_RANGE
0	C	02.01.2020	02.01.2020	2020-01-02:2020-01-02
0	I	03.01.2020	03.01.2020	2020-01-03:2020-01-03
0	M	04.01.2020		2020-01-04:null
1	Z	02.01.2020		2020-01-02:null
4	N	06.01.2020		2020-01-06:null
5	C	01.01.2020		2020-01-01:null
7	U	01.01.2020		2020-01-01:null

Your pasting didn't work because the forum doesn't know what to do with thead and tbody tags. Removing these seems to work.

Now - what's the expected outcome based on this data?

lolxd · ‎10-02-2023

thanks,

this is the expected outcome

it depitcts the count of "covering ranges" from `fact_table_range` for each date from `calendar_daily` ie something like count of rows returned from this select:

select * from calendar_daily inner join fact_table_range on calendar_daily.date >= DATE_FROM and (DATE_TO is null or calendar_daily.date <= DATE_TO)

for given calendar_daily.date. This "logic" is done via the `dimension_table_range`, which translates any date from `calendar_daily` to all possible "covering ranges" from `fact_table_range`, as seen here.

It works fine with such small dataset, the problem starts when i use greater dataset with three years worth of days in `calendar_daily` and millions of rows in `fact_table_range` (the size of `dimension_table_range` is determined by unique tuples of (`date_from`, `date_to`) in the fact table, the cap for dataset tracking three years is somewhere around 210K rows). The outcome for a dataset of that size is

as shown my original post

lbendlin · ‎10-02-2023

your 'dimension_table_range' table does not look happy. It is clearly not a dimension table. I think you want to review and refactor your data model before you work on the visual issues.

lbendlin · ‎09-26-2023

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that 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.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...