Issue with Filtering a Line Chart with Fixed Axis

[Mann]

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:

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: 

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.

[Anonymous]

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.

[Mann]

Hi

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

[Anonymous]

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

[Mann]

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]

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

[Mann]

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.

[v-lili6-msft]

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:

and here is sample pbix file, please try it.

Regards,

Lin

[Mann]

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

[v-lili6-msft]

hi  @Mann 

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

Regards,

Lin

[Mann]

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.