Hi everyone,
I’m trying to solve a Tableau issue involving FIXED LOD calculations, a Max Run Date context filter, row-level highlighting, and a blended data source.
Background
I have a dashboard with two sheets/tabs.
Sheet 1 – New Prescribers
This sheet identifies New Prescribers at the HCP level.
The sheet has the following filters:
- Max Run Date Flag = True — in context
- Trial = True
- User Filter = True
- Several territory/region dimensional filters
My Avg Patients calculation is:
{ FIXED [NPI], [Month] :
AVG([unique_patients_yutrepia_winrevair_zinc_cvs])
}
My New Prescriber calculation is:
ZN([Avg Patients]) > 0
AND
{ FIXED [HCP] :
MAX(
IF [Run Date] < { MAX([Run Date]) }
AND ZN([Avg Patients]) > 0
THEN 1
ELSE 0
END
)
} = 0
Sheet 2 – Summary/Output Table
The second sheet is an output table that is being rendered through a SuperTable extension.
The row dimensions are approximately:
HCP
Institution
CPS Region
ALD Region
NPI
CPS Territory
ALD Territory
NPI Specialty
State
ZIP
...
The table also contains monthly patient measures such as:
Jun Est Pts
Jul Est Pts
Aug Est Pts
I am not displaying the New Prescriber field as a column.
Instead, I need to use New Prescriber only to highlight the entire row of the 5 New Prescribers in SuperTable.
The problem
Initially, I did not have Max Run Date Flag in context on Sheet 2.
I placed the New Prescriber field on Detail so that SuperTable could use it for row highlighting.
This worked correctly:
The table had the correct number of rows.
The 5 New Prescribers could be identified/highlighted.
However, I also have a ratio calculation involving two data sources:
Yutrepia Patients → Primary data source
Shipment Patients → Secondary/blended data source
Without Max Run Date Flag in context, the ratio does not return the expected values.
When I add Max Run Date Flag to Context
The ratio/counts become correct.
However, once Max Run Date Flag is in context, putting the New Prescriber field on Detail causes the table to split/increase rows.
For example, an HCP that should appear as one row can effectively get split because of the New Prescriber value at the underlying record level.
I don't want New Prescriber to change the existing row grain. I only want it to act as a highlighting flag.
So I currently have this conflict:
Setup Result
Max Run Date NOT in context Correct row count + New Prescriber highlighting works, but ratio is incorrect
Max Run Date IN context Ratio is correct, but New Prescriber on Detail causes additional/split rows
What I am trying to achieve
I need all of the following simultaneously:
Keep Max Run Date Flag as a context filter because it currently makes the blended ratio calculate correctly.
Identify the same 5 New Prescribers as Sheet 1.
Use the New Prescriber result only for row highlighting in SuperTable.
Do not add New Prescriber to the dimensional grain or create additional rows.
Keep the Yutrepia Patients / Shipment Patients ratio accurate.
Maintain the existing HCP/NPI/institution/etc. row structure.
My questions
1. Why does adding Max Run Date Flag to context cause the New Prescriber field on Detail to split/increase the rows?
I understand Tableau's order of operations, but I'm not clear on exactly how the context filter is interacting with my FIXED [HCP] calculation and the existing row grain.
2. Is there a way to calculate the New Prescriber flag at the HCP level and expose it to SuperTable without making it part of the mark's dimensionality?
For example, should I be using an aggregated version such as:
MIN([New Prescriber Flag])
or
ATTR([New Prescriber Flag])
instead of placing the dimension directly on Detail?
3. Is my current New Prescriber LOD logically correct when Max Run Date Flag is a context filter?
The calculation needs historical Run Dates because it checks:
[Run Date] < { MAX([Run Date]) }
But the context filter may be restricting the data before the FIXED LOD is evaluated.
Could this be the reason for the unexpected behavior?
4. Most importantly, how can I solve the ratio and highlighting requirements without increasing the row grain?
Would the recommended solution be to:
change the LOD,
create a separate HCP-level flag,
use a separate data source/relationship,
use a table calculation,
use an aggregated field on Detail,
or handle the Max Run Date differently?
One additional detail
The Avg Patients calculation is:
{ FIXED [NPI], [Month] :
AVG([unique_patients_yutrepia_winrevair_zinc_cvs])
}
while the New Prescriber logic is fixed at [HCP].
The output table itself contains several dimensions below/alongside HCP.
So I suspect there may be a grain mismatch between [NPI] + [Month], [HCP], and the output-table grain, but I'm not sure whether that is the actual root cause.
What would be the recommended Tableau approach to achieve this without duplicating/splitting the rows?
this is from databricks sourc and i don;t have access to those sources so couldn't share the .TWBX file part of confidential.
Thanks!
This correctly identifies 5 HCPs as New Prescribers
on Sheet 1.
#Tableau Desktop & Web Authoring #LOD
Without having a look at your packaged workbook (twbx) and understand the complete data model, I cannot suggest a solution that would work. Having a blended data source often causes issues and so you may want to see if you can get rid of it and use it in a join or as a relationship.