topic Adjusting use relationship formula to include specific measure values on line graph in DAX Commands and Tips

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

Anonymous — Tue, 17 Nov 2020 16:50:38 GMT

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

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):

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

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

lbendlin — Wed, 18 Nov 2020 02:40:21 GMT

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?

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

Anonymous — Wed, 18 Nov 2020 12:38:28 GMT

To compare counts that come in on :

1. Notication date

2. Epi date

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

Anonymous — Wed, 18 Nov 2020 15:27:06 GMT

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.

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

Anonymous — Wed, 18 Nov 2020 15:34:44 GMT

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] ) ) )

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

Anonymous — Wed, 18 Nov 2020 15:38:55 GMT

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.