cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Adjusting use relationship formula to include specific measure values on line graph

I have 2 rolling 14 day average calculations and Im hoping to display them (highlighted) on the same line graph:

 

Notification vs. Epi4.PNG

 

However I've tried to create a userelationship measure to get them on to the correct axis and I'm getting the correct numbers for Epi Count but  Event count values are coming in as counts rather than the ones I need (highlighted  on left below):

Notification vs. Epi3.PNG

 

I'm using this measure:

Cumulative Rate - Event Count = CALCULATE(COUNTX('Sheet1',[Prev 14 Day Event Count]/4761865*100000),USERELATIONSHIP(Sheet1[Event Date],DimDate[Dates]),NOT(ISBLANK('DimDate'[Dates])))

 

Can anyone help get the Cumulative rate -Event counts on to the line graph

 

SEE FILE ATTACHED

https://www.dropbox.com/s/ksi9fm0kbzpj1hg/Notification%20vs.%20Epi.pbix?dl=0

5 REPLIES 5
Solution Sage
Solution Sage

In your measure

Cumulative Rate - Event Count =
CALCULATE(
    COUNTX(
        'Sheet1',
        [Prev 14 Day Event Count] / 4761865 * 100000
    ),
    USERELATIONSHIP ( Sheet1[Event Date], DimDate[Dates] ),
    NOT (
        ISBLANK( 'DimDate'[Dates] )
    )
)

you are using COUNTX. Obviously, this will always return an integer, not a number between 0 and 1. From the documentation of COUNTX: Returns the number of values that are non blank by iterating the provided table. How then is it possible that your pictures show values between 0 and 1? I think your measure should be something like

Cumulative Rate - Event Count =
CALCULATE(
    COUNTX(
        'Sheet1',
        [Prev 14 Day Event Count] 
    ) / 4761865 * 100000,
    USERELATIONSHIP ( Sheet1[Event Date], DimDate[Dates] ),
    // If you need this condition here,
    // it means something's wrong with
    // the model. A date table should
    // never store BLANKS in any of
    // its columns.
    NOT (
        ISBLANK( 'DimDate'[Dates] )
    )
)
Solution Sage
Solution Sage

If you have 2 date columns in a fact table and one Dates table that joins to both fields, then in order to show 2 measures dependent on those 2 columns on one axis, you have to use the columns from Dates. If you use a date column from the fact table, one of those measures will always be screwed up. By the way, all columns in fact tables MUST always be hidden and one can slice and dice only via dimensions. Deviate from this and you'll be creating hell for yourself and your users. Tried and tested. Do not make the same mistakes.

Super User III
Super User III

what is your rationale for the bidirectional search filter between the calendar table and Sheet1 ? What made you choose the Epi Date and Event Date as part of the X axis well?

To compare counts that come in on :

 

1. Notication date

2. Epi date

By the way, you should refrain yourself from enabling bi-directional filters in your model. Such a setup is dangerous (to say the least) and should only be used when you fully realize the consequences. It's absolutely not a technique to do what you use it for. Please use one-way, standard relationships if you want to be able to explain what your model is doing.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.