Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Mann
Resolver III
Resolver III

Issue with Filtering a Line Chart with Fixed Axis

Hi Guys,

 

I am showing distinct count of individuals as per time (5min interval) as per zones.

 

I have three tables:

1) Source: It has data for Individual IDs present in different zones as per 5 min interval across days. There is a column as per shift types here: "Day Shift" (6AM to 6PM current day) and "Night Shift" (6PM current day and 6AM next day)

2) Fixedaxis: This is the calculated table created to fix the x-axis of line chart as per shifts. 

3) Shift Type: This is a separate table which only has two rows of "Day Shift" and "Night Shift". This is used in slicer of shift type.

 

NOTE: I need to use Shift Type Table as a slicer on report, I can't use Shift type from Source table for this due to other modelling constraints.

 

This is how the data model looks like:

Model.PNG

 

I am creating a line chart with x-axis coming from "Fixed axis" table as I need to fix the start and end time as per shift type like:

Day Shift (DS): 6AM to 5:55PM

Night Shift (NS): 6PM to 5:55AM next day

 

I created this measure for showing data for Zone: A and D

ADZone = 
VAR _DisCount =
    CALCULATE (
        DISTINCTCOUNT ( 'Source'[IndividualID] ),
        FILTER(Source,
               'Source'[Zone] IN { "A","D" } 
        ))
RETURN
    IF ( ISBLANK ( _DisCount ), 0, _DisCount )

Shift Type slicer is from Shift Type table. 

 

This is how the line chart is coming: 

 

Report.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

This issue is:

  • When Day Shift is selected I am getting data for both DS and NS for a single day. Although NS values are "0" when Day Shift is filtered. I want axis to start from 6AM to 6PM only for DS.
  • I want filters to work from Shift Type table only. If I use Shift Type filter from Fixedaxis: Chart is getting filtered fine.

 

Let me know what issue I am facing here might. Seems like some issue with context transition.

 

I can share the sample report if needed.

 

Mann.

 

10 REPLIES 10
Anonymous
Not applicable

I would recommend changing your relationship cardinality to single such that the arrow is pointing away from the one and towards the many. 

Not sure if that will solve the problem, but that's a good place to start. There's likely ambiguity being introduced into your scenario.

Hi

I have tried with single filter there but it didnt help. No change.
Anonymous
Not applicable

Can you provide the sample file to take a look at?

Hi @Anonymous 

 

I added some sample data and created a test PBIX. Its present here:

https://drive.google.com/file/d/1MG80plgaNLlRYlIjQ9mCat3iQ8zLO8X4/view 

 

Let me know if you need anything else.

 

Mann

Anonymous
Not applicable

The issue appears to be the date and time relationships. Because you're pulling data from Source and using the fixedaxis table as a dimension, and because those 2 tables don't have a time series link, it's pulling in all the data. 

I created a separate date table and time table and connected those to the Source table and that seems to work as expected. 

 

Let me know if this is what you're looking for. 
https://drive.google.com/file/d/18TbLs7cePoUJC1ObW0YX_Mw3a9ygOGJU/view?usp=sharing

Hi There,

Thanks for checking on this. Unfortunately this solution wont help me since I need shift type in fixedaxis table. I realized the issue is with the measure. If I convert that line chart into table visual and remove this measure then records are getting filtered by shift type as expected.

hi @Mann 

For your case, you should create the relationship between Fixedaxis table and Source table by Date Time column and Intervals column.

and adjust the formula as below:

ADZone =
VAR _DisCount =
    CALCULATE (
        DISTINCTCOUNT ( 'Source'[IndividualID] ),
        FILTER(Source,
               'Source'[Zone] IN { "A","D" }
        ))
RETURN
    IF ( ISBLANK ( _DisCount ), BLANK(), _DisCount )

 

Result:

10.JPG11.JPG

and here is sample pbix file, please try it.

 

Regards,

Lin

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

Hi @v-lili6-msft 

 

Thanks for checking on this. This change is also not helping me since this defeats the purpose of having fixed start and fixed end time on x-axis.

If we replace 0 by Blank(), it helps in restricting records of other shift type on x-axis in line chart but it also removes the fixed start and fixed end time. 

For example: If for DS, last non blank values is at 5:00 PM then x-axis is shown till 5:00PM only. I want x-axis to have fix start and fix end time as per fixedaxis.

 

Thanks

Mann

hi  @Mann 

you could enable show items with no data for [Date Time]

14.JPG

 

Regards,

Lin

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

Hi Guys

I had to use Shift Type filter from Fixed axis only to make this work.
Somehow applying filter from ShiftType table doesnt work for this measure.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors