Multiple dates and cumulative totals

Hi

I have got myself in a pickle! I have several dates that I need to use for measures. Each measure on its own is OK when I use either the date from the fact table or USERELATIONSHIP.

BUT, for example, how do I do cumulatively totals in this situation.

On a report page, I have several visuals using different dates that need to work with a date slicer.

I can’t get the time intelligence working as it should, yet I’m sure this is possible.

Can anyone out there ‘sort me out’? Thanks Erica

@Ericadyson,

Not enough info here for me to specifically diagnose the nature of the problem or offer a specific solution, but @Greg has posted outstanding resources and strategies for managing multiple dates in the Events in Progress DAX Patterns section.

  • Brian

I’d need to see a PBIX file and a mockup of the results you want to see to make sure, but my initial gut reaction here is to perhaps get rid of the relationships entirely, and just do everything virtually via TREATAS and a slicer based on a disconnected date table.

Sounds like a cool problem. If you can please provide the above info, I’d be eager to take a crack at it.

Thanks!

  • Brian

Hi Brian
First, thanks a speedy response. This problem is a bit urgent actually. Problem one, the tables are in Hebrew. But here is the summary. It’s all about Corona virus for a specific town. I’m producing a daily dashboard based on stats from the government.

2 fact tables… people who are ill and fact table 2 those who are in isolation.
I added a date table and created 1 main relationship between the date table and the date people were added to the list (for each table).

I then create inactive relationships and use USERELATIONSHIP for some of the measure. As I said, the measures work OK on their own, but not with a date slicer.

count of people in isolation, out of isolation, cumulative totals and daily totals for both
count of people with the virus, in hospital, left hospital - cumulative and daily totals

I have lots of dates that I use for these measures but as I said, none of them work with a date slicer, so I can’t get history dynamically. I only have the current status on the dashboard.

I can send you the .pbix but you won’t be able to read it! Grrh.

Example measure

people in isolation = calcuate([total count of patient ids],filter(isolation[isolation[end isolation date]>=today())

Cumulative attempt

var cumulnotinbidud=

CALCULATE([total in isolation]-[left isolation], DATESYTD(‘Date’[Date]))

return

CALCULATE(cumulnotinbidud,USERELATIONSHIP(Bidud[end isoltaion],‘Date’[Date]))

Does any of this make sense? I’m in a pickle as you can see!

@Ericadyson,

OK, this is always dicey without a file to test it out on, but let’s give it a go:

  1. Create a table via the following DAX expression

Disconnected = VALUES(Date)

  1. Now create a date slicer from the Date column of the Disconnected table you just created above

  2. Create two simple measures, [Harvest DMin] and [Harvest DMax] to grab the min and max values of your disconnected date slicer above

  3. To each of your relevant measures add a filter condition that will look something like this:

    Measure Name =

     CALCULATE(
     	Expression,
     	FILTER(
     		'Relevant Table',
     		'Relevant Table'[relevant date] >= [Harvest DMin] &&
     		'Relevant Table'[relevant date] <= [Harvest DMax]
     	)
     )
    

See how this goes. If it doesn’t get you what you need, perhaps you can create me a simple, scaled-down example file that I’d be able to read that we can use to work through a more specific solution. But I think this general approach is on the right track.

Hope this is helpful.

  • Brian

What’s the HarvestDmin /max? Not seen that before? Do I enter exactly those words?

@Ericadyson,

No, just two measures below that grab the lower and upper endpoints of your disconnected date slider:

Harvest DMin = 
    CALCULATE(
        MIN( Disconnected[Date] ),
        ALLSELECTED( Disconnected[Date] )
    ) 

 Harvest DMax = 
        CALCULATE(
            MAX( Disconnected[Date] ),
            ALLSELECTED( Disconnected[Date] )
        ) 

When you’re beyond your time crunch, you will probably benefit from this video I did as part of @melissa’s and my time intelligence series, which walks through a whole array of useful date harvesting techniques.

  • Brian

@Ericadyson,

Just checking in to see how it’s going? Not sure what your deadline is, but I have time this weekend to keep working through this with you if needed.

  • Brian

Hi. Thanks for checking back. Went to bed! But in the middle of the night I realised… it’s all about dates, so I just need to change the field names and you’ll be able to see the issue! Silly me. Will send pronto. I will also, in parallel, work on what you kindly sent me.

Beit Shean Corona 070-01 for brian.pbix (215.9 KB)

Beit Shean is a small town of 15,000 people… just so you know. I live 5 minutes from there on a kibbutz called Hamadia… we’re 3 kms from the Jordan border.

@Ericadyson,

Thanks for sending the file. In looking at it, I’m thinking that this may really be more of a data modeling issue than a DAX issue. The first thing is that you want to eliminate the bidirectional relationship between your fact tables. In a proper star schema model, fact tables should not be connected with each other - the relationships should flow only via the dimension tables.

But the bigger issue is that I don’t think two separate fact tables are needed here. The structures are very similar, and it seems if you add a status field (reflecting either isolation or hospitalization) you could easily combine the two tables, thus eliminating half the relationships in your model, and substantially simplifying the problem at hand, since while you would now have to filter on status, you would only have to turn on one relationship for both status conditions and everything could be linked to a single data slicer. What do you think?

Also, on what page(s) do you want to put the date slicer and if on both pages, do you want the slicer values to synch between pages?

Finally, I’m assuming you would want a date range slider, not a single day selection, correct?

Thanks. Hope you’re staying safe. Those infection rates and the upward trend looked quite troubling for such a small area.

  • Brian
1 Like

Hi

Thanks for that.

  1. Not sure why I added the relationship between the 2 fact tables. It’s not used… but somehow I thought maybe I would need it! Not done that before!

  2. I thought of combining the tables but was worried about duplicates appearing in the person IDs… I think there probably are, or will be. The source is the Ministry of Health and for sure, the records are not checked … and the data will be added by clerks in different departments no doubt. So that’s why I didn’t combine. My queries delete duplicates within each file but I’d be worried about deleting duplicates if they were combined. See what I mean.

  3. If I use time-related measures, how do I do the calc… so I can get the numbers for people in and out of isolation. Currentlly I am using a filter with <=today… but I can’t do that with time related intelligence, can I?

  4. No the date filters would be separate. I’m not keen on that kind of syncing, I find that people get mixed up and forget that there was a filter. Also, different people will use the general page and the tracker page.

Have I made myself a mess? It was all done is such a rush… from one day to the next, you know… small town, very very disorganized… and I’m a freelancer and suddenly they need it all yesterday. Nobody’s asked for time intelligence but I know I will need it.

1 Like

@Ericadyson,

In response to your points above:

  1. The reason not to do that is that once the bi-directional relationship is activated, you create an ambiguous path (i.e. two different routes) between your date table and each fact table. For example, when the bidirectional link is activated, here are the routes that can be taken to get from the date table to the isolation table:

Ambiguous paths can make DAX operate erratically, returning nonsense results even to properly created measures, or worse yet inaccurate results that aren’t obviously nonsensical. If you’re interested in reading more about this issue, this article goes through a number of examples in depth:

  1. It’s absolutely fine to duplicate Person IDs in your fact table. Often in the optimal “narrow and long” (lots of rows, relatively few columns) fact table structure, you will have multiple records per person/transaction. In this case, depending on the measure you will address this duplication either by 1) filtering on status (a patient will either be hospitalized or in isolation, but not in both states at the same time) or 2) for measures that don’t use a filter on status, you will use a construct that removes the duplication such as COUNTROWS( VALUES( Data[Person ID] )). You will need to ensure that in Power Query you are not eliminating “duplicate” rows that are not in fact duplicates.

  2. Once you have your fact table set up this way and connected to your date table via inactive relationships, you can use the classic Events in Progress pattern to count/analyze patients in different status conditions across time. As I mentioned in my earlier post, in the new DAX Patterns section of the forum, @Greg does a great job explaining how this works, and providing lots of examples and links to other forum posts where this pattern is used.

  1. That makes sense. In terms of making people aware of what filters are active (and speaking of @greg), I love the technique he used in his entry for Data Challenge #3, where using very little space, he mirrored back all the difference slicer choices. Take a look and see what you think – might be a useful technique to consider here or in the future. I’m definitely going to steal it for some of my future reports…

I don’t think you made a mess, and I definitely understand how time pressure makes this more difficult, but I do think that backtracking a bit and revisiting/revising your data model now will put you in better position down the road to do time intelligence and other analyses that may become important. However, the “best” data model is the one most suited to the questions and analyses you need to do, and only you know what those are. But hopefully the information above gives you an alternate structure to think about how those questions might be addressed more directly and easily. (Note: I do really like the design and organization of your report).

I hope this is helpful. If you have a mockup of specific analyses you want to do that you’re having trouble with, just give me a shout and I’m happy to work through them with you.

Good luck, and thanks for this important work that you’re doing.

  • Brian
3 Likes

Hi
I’ll need to read and think about the changes so I understand what I’m doing (not just parrot fashion). For now I am having difficulty getting these 2 visuals right. So Brian if you cobble together the measures I need, I would be eternally grateful. I could then (just for now) copy parrot-fashion (something I hate doing).

Because for isolation I have no start date, only an end date… given the crappy model I’ve got right now I need

2 chart visuals that gives me accurate data for
chart 1 -cumul by date - total in isolation (graph goes down)
chart 2 cumul by date total out of isolation (graph goes up)

That’s what is “killing me” at the moment. Hospital table is OK because I status field, so it’s easy.

I don’t have a status field in the isolation table… how can I get one for each date?

So if you could help me out and get me “over the hill” for tomorrow, that would be great, but I understand if you feel that’s not the right thing to do!

1 Like

Apart from merging into 1 fact table, I think a lot of issues will now be resolved because, now after thinking about this and using the data for a while, I realise that the key relationship in the Isolation table is, in fact, the end isolation date! With that established, and the fact that I generated a start isolation date (14 days before the end date), I think I can now use patterns, and time intelligence. I need to explore a bit more. The hospital side is rather easier to deal with, but again, I think I can find a good primary key. If you come up with some cobbled together solution for me for now, great, if not, don’t worry Brian, I am avoiding my cumulative charts for now, and just showing daily movements! I don’t think they will know the difference, frankly!

@Ericadyson,

Sounds like great progress. I’m happy to work on fixing the cumulative totals today. Is the file I have the most up to date one, and is everything I’ll need is in the English columns?

Thanks.

  • Brian

Well from a data point of view, yes because I haven’t had an update for a couple of days. Put English names to my pathetic attempts at generating cumulatives for isolation… so you now you can see what I was trying to do :-). Anyhow, use this version. Thanks so much.
------------------Beit Shean Corona 070-01 for brian2.pbix (216.2 KB)
Re merging, I’m a tad nervous about duplicates… so I should merge shouldn’t I, add new fields as needed, and accept data from both tables. That’s right isn’t it? But then I run into the problem of how to link to the date table. Are you saying that I shouldn’t have any active relationships and then use the date harvesting that you outlined yesterday?

@Ericadyson,

I’m a bit worried about changing too many things at the same time. Let’s work for the moment within the context of your current data model, get the cumulative totals right so that you can provide the information needed ASAP, and then when the time pressure is off a bit, we can step back and rework that.

No need at the moment for the harvesting/disconnected slicer approach – I think we can make do without having to go down that road. In terms of joining the two tables, the way I would do it would be to get the structures as lined up as possible between the two tables (remove any unnecessary columns, rename columns as consistently as possible across the two tables), then for each one add a column called Status or Source (just a way to differentiate between the two once they’re combined), and in that column fill with “Isolation” for the first table, “Hospitalization” for the second table, then append (not merge - we want to create new rows, not new columns) the two tables into a new combined table.

I’ll dig in on the cumulative total work this afternoon and get back to you later today.

  • Brian

P.S. Thanks for the information about the kibbutz. I googled it, since it’s always interesting to know more about the background of the people, places and issues behind these data models and DAX measures that we work on.

Sorry, I mislead you. Definitely not going to do all this at once; I’m getting my head around things. I think I need to merge not append because the 2 tables are not the same. The hospitalization table has quite a few fields that the other table doesn’t have. If I append, I won’t get all the extra fields I need. Anyhow, let’s park that for now and concentrate on getting this model as right as I can. I only started this late last week and didn’t have time to work and prototype… so I’m paying the price for that now! I’ll work on the combining and checking things out. And of course, the extra burden is that it’s all in hieroglyphics ie Hebrew!!

You won’t lose those fields in an append. They will be pulled over for the hospitalization part of the combined table and just be given null values for the Source = Isolation records. But that’s an issue for another day… :wink:

Will get working on this after lunch, and hopefully back to you later this afternoon w/ a solution.

  • Brian