Forum Discussion
line chart visual bug
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">date</th> </tr></thead> <tbody><tr> <td>01.01.2020</td> </tr> <tr> <td>02.01.2020</td> </tr> <tr> <td>03.01.2020</td> </tr> <tr> <td>04.01.2020</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> |
4 Replies
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-Forum/ba-p/963216
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/1447523DATE_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?
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
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.
