Incorrect Results in chart but correct results in virtual table

[SergioTorrinha]

Hi everyone!

I'm facing a issue a little bit tricky to explain, so lets see if I can manage to make myself clear enough to get some help/assistance here.
A sample of my current data model is provided in below image.



My issue is the following:
I built a virtual table to calculate active patients (given a complexactive patient definition) with the help of DAX Studio, and that virtual table is retreiving correct results  at every single level of granularity (already checked using DAX Studio aswell).
However, and for my dispair  at this moment since I'v tried everything I know to fix this including using data lineage with TREATAS, when I use a mesure that uses that virtual table to display data in line chart (chart has evolution by date coming from dimDates, and by precriber type coming from dimPrescriberType), the values are not correct in every granularity level.

The metric I am using is the following:

Actual Patients = 

/*
The set of rules for the Actual Patient count, in month X, is the number of patients that:
    1) ordered 1 or more packs in month X or the preceeding 4 months 
    plus
    2) the patients who ordered >1 pack before the X preceeding months and covered minimum 1 of the X preceeding months, 
        assuming 45 days coverage by 1 pack and only taking into account the last order
*/

--calculates Min and Max Order date, in order to filter  further ahead
VAR MinOrderDate = MIN( facOrders[Date_receipt] )
VAR MaxOrderDate = MAX( facOrders[Date_receipt] )

--selected month stop (to simulate what the user would select iin PBI slicer)
VAR SelParameter = SELECTEDVALUE( parStopMonths[Stop Months] )

--selected date period
VAR SelDate = SELECTEDVALUE( dimDates[Date] )
--calculates the GroupbyTable only with time periods and Prescriber ID's
VAR GroupbyTable = 
		CROSSJOIN(
					SUMMARIZE(
						FILTER( dimDates, dimDates[Day] = 1 && dimDates[Date] >= MinOrderDate && dimDates[Date] <= MaxOrderDate ),
						[Date],
						[YearMonth]
						),
					VALUES( dimPatients[PatientID] )
				)

--calculates when a patient is Actual or not
VAR ActPts = 
		FILTER(
			ADDCOLUMNS(
			GroupbyTable,
			"ActPt", 	
					VAR MinDate 	= CALCULATE( MIN( dimDates[Date] ) )
					VAR StartDate 	= EOMONTH( MinDate, - SelParameter - 1)
					VAR SelPatient 	= [PatientID]
					VAR OrderQtyPeriod = CALCULATE( COUNTROWS( facOrders ) , dimDates[Date] > StartDate && dimDates[Date] <= MinDate, facOrders[PatientID] = SelPatient )
					
					VAR PacksQtyBeforeStartDate = CALCULATE( SUM( facOrders[Packs] ), dimDates[Date] <= StartDate , facOrders[PatientID] = SelPatient )
					
					VAR MaxOrderDateBeforeStart = CALCULATE( MAX( facOrders[Date_receipt] ), dimDates[Date] <= StartDate, facOrders[PatientID] = SelPatient )
					
					VAR PacksQtyMOBS = CALCULATE( SUM( facOrders[Packs] ), dimDates[Date] = MaxOrderDateBeforeStart, facOrders[PatientID] = SelPatient )
					
					VAR MaxCoverageDate = EDATE( MaxOrderDateBeforeStart, ROUNDDOWN( PacksQtyMOBS * 45 / 30, 0) )
					
					RETURN
						SWITCH(
								TRUE(),
								OrderQtyPeriod > 0, 1,
								PacksQtyBeforeStartDate > 1 && MaxCoverageDate >= MinDate, 1,
								BLANK()
							)
			),
			[ActPt] = 1 
		)

--returns prescriber type
VAR PrescType = 
		ADDCOLUMNS(
							SELECTCOLUMNS(
                                        ActPts,
                                        "Date", [Date],
                                        "PatientID", [PatientID]
                                        ),
							"PrescriberID",
											VAR MinDate 	= CALCULATE( MIN( dimDates[Date] ) )
											VAR MaxOrderDateBeforeSelPeriod = CALCULATE( MAX( facOrders[Date_receipt] ), dimDates[Date] <= MinDate  )
											VAR SelPatient 	= [PatientID]
											
											RETURN
											CALCULATE( MIN( facOrders[PrescriberID] ), dimPatients[PatientID] = SelPatient, dimDates[Date] = MaxOrderDateBeforeSelPeriod ),
							"PrescriberType",
											VAR MinDate 	= CALCULATE( MIN( dimDates[Date] ) )
											VAR MaxOrderDateBeforeSelPeriod = CALCULATE( MAX( facOrders[Date_receipt] ), dimDates[Date] <= MinDate  )
											VAR SelPatient 	= [PatientID]
											
											RETURN
											CALCULATE( MIN( facOrders[PrescriberType] ), dimPatients[PatientID] = SelPatient, dimDates[Date] = MaxOrderDateBeforeSelPeriod ),
							"BrickID",
											VAR MinDate 	= CALCULATE( MIN( dimDates[Date] ) )
											VAR MaxOrderDateBeforeSelPeriod = CALCULATE( MAX( facOrders[Date_receipt] ), dimDates[Date] <= MinDate  )
											VAR SelPatient 	= [PatientID]
											
											RETURN
											CALCULATE( MIN( facOrders[BrickCode] ), dimPatients[PatientID] = SelPatient, dimDates[Date] = MaxOrderDateBeforeSelPeriod )	
						)

--dealing with data lineage
VAR ActPatPresc_Lin = 
			TREATAS(
					PrescType
					,dimDates[Date]
					,dimPatients[PatientID]
					,dimPrescriber[PrescriberID]
					,dimPrescriberType[PrescriberType]
					,dimGeography[BrickCode]
			)
		

RETURN

SWITCH( 
    TRUE(),
    SelDate <> BLANK(),
        CALCULATE( DISTINCTCOUNT( dimPatients[PatientID] ), ActPatPresc_Lin),
    CALCULATE( DISTINCTCOUNT( dimPatients[PatientID] ), ActPatPresc_Lin, dimDates[Date] = MaxOrderDate ) 
)

At this point I can't share any data as it's very sensitive.
Can you please sugest any resources to go for, in order to try fix this issue, please?
I already read data lineage blog, by Alberto and Marco, but it didn't helped me much besides teaching me what data lineage is (which i thought to be the source of this problem).
Thank you in advance

[v-robertq-msft]

Hi, @SergioTorrinha 

In your DAX formula, you defined multiple tables and values. When I encountered problems in a complicated DAX formula, I usually split DAX into several parts for step-by-step testing. To test the tables I defined, I usually copied the DAX I defined to create a new table and check the result to ensure if the table is what I really need. To test the variables I defined, I return them in the DAX and place them into the chart to test if the value is correct.

Maybe you can also split the DAX formula into several parts to doing some tests. (If you want to use a column chart, you can place each part into it and check the value). Or you can just use more than one measure to achieve this, which will not have a big impact on efficiency

How to debug a DAX measure

Best Regards,

Community Support Team _Robert Qin

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

[v-robertq-msft]

Hi, @SergioTorrinha 

What do you mean by “it was marked as spam for some reason”? Have you found a solution to this problem?

It’s very hard to find the true reason for this problem only with the DAX formula and the relationship view.

You can try to change the filter directions between the tables in your data model to “Both” and check if the value can become true.

Best Regards,

Community Support Team _Robert Qin

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

[SergioTorrinha]

Hi @v-robertq-msft 

Thanks for your feedback and for taking the time to look at my DAX code.

What you refer on your answer, is something that I have done, before coming here to the forums to ask for help, and that's why I already refer to in in my first post (although perhaps it was not clear enough).

As you could see, in my code, I have defined only essencial tables to my use case (if you find it relevant I can explain breifly what was the logic behind the virtual tables construction, just let me know) and I have also materialized the final virtual table (ActPatPresc_Lin) into a DAX table. After this, I ensured that on PBI the results retreived by this table were the correct ones - I built some simple charts and used slicers from columns of dimensional tables connected to facOrders, and also used some other columns (from dimensional tables connected to facOrders) in charts to create the correct filter context. So, I'm pretty sure that the results coming from the virtual tables are absolutly correct.
With this in mind, probably, the problem is with the last part of my code is below the RETURN statement. Do you think the same? Do you have any sugestion of improvement for this one?

At this point you might be wondering why am I not using the matearilized DAX table to solve the problem.
I do not want to do this, for 2 main reasons:

1 - the solution I would like to create, is to "generate" and new virtual table ActPatPresc_Lin, every time a report user uses a specific slicer in the dashboard that will basically return a different value for this part of my code:

--selected month stop (to simulate what the user would select iin PBI slicer)
VAR SelParameter = SELECTEDVALUE( parStopMonths[Stop Months] )

2 - DAX tables might pose performance issues, something I would like to avoid to ensure good user expirience in the end.
Specifically in this cenario, I must use a crossjoin between the dates where there was orders and the patients ID's, in order to ensure that even in a month where a patient does not make any order he can still be counted as actual patient if he respects the conditions previously mentioned. I'm afraid this crossjoin might "blow in my face" in the future with the growth of ID's and dates.

Again, thanks for you intput and for taking the time to look into this.

[SergioTorrinha]

Hi @v-robertq-msft , thanks for reaching out!

Answering your questions one-by-one:

What do you mean by “it was marked as spam for some reason”?

After I posted this thread, I wasn't able to see it in the respective area of the forums. So I posted (somewhere, here on the forums, which I do not remeber exactly where), if some moderator could check if the post was marked as a spam in an automated fashion.
Aparently it was, regarding one of your collegues answer.
However, this, for me, is not very important but yet is important to Microsoft, because some users messages are automaticly being marked a spam without, in fact, being a real spam thread.
What is important, to me, is having some help on the topic so I can keep proving that PBI is the best tool for the job at hand (and others...).

Have you found a solution to this problem?
Unfortunatly, no. I need some help, if possible. 🙂

It’s very hard to find the true reason for this problem only with the DAX formula and the relationship view.
I aknowledge it is, in fact, hard to find the true reason of the issue even with the data.
However, if you want to have something to test, plese feel free to use Advancedworks database (the orders fact table should be the main data source) and, with the picture it is possible to draw the cenario (I'm sorry but i don't have it installed on my system).

You can try to change the filter directions between the tables in your data model to “Both” and check if the value can become true.
I'm sorry, but I cannot see how the directions of the relationships in  the tables of my data model will help in this regard, since I'm having correct results in my virtual table and only have an issue, the moment I pass the results into a measure to be displayed in a line plot.
Also, all my dimensions are filtering my fact table, exactly following relations ships in a start schema best practices.
Mind explaining a bit further on how are the relationships directions help on this?

Again, thank you for your assistance. 🙂

[SergioTorrinha]

Am I allowed to bump this post, as it was marked as spam for some reason ?
Thank you 🙂