Help with bi-directional filter and DISTINCTCOUNT measure speed

[jdbuchanan71]

Good afternoon,

I'm hoping someone can help me fix a measure that is running very poorly because (I believe) of a bi-directional relationship in my model and the nature of the count that is being done.

The model is for healthcare claims data and the agents that sell the policies, just a couple of notes on the data:

• Agents cover multiple groups
• More than one agent can be assigned to the same group

This is why there is a bi-directional relationship between vAGENT and vGROUP.   

This then flows down to the claims where I am doing the counts and the count changes slightly based on the claim type.

Claim Count = 
CALCULATE (
    DISTINCTCOUNT ( vCLAIM[Client_Batch_ID] ),
    KEEPFILTERS ( vCLAIM[Claim Type] IN { "Life", "Disability" } )
)
+ 
CALCULATE (
	DISTINCTCOUNT ( vCLAIM[Client_Claim_ID] ),
	KEEPFILTERS ( vCLAIM[Claim Type] IN { "Dental", "Flex", "Institutional", "Pharmacy", "Professional" } )
)

The problem I am seeing is the way the engine is calculating the measure against the agents, it is generating a scan for almost ever agent in the table (784) and each DISTINCTCOUNT in the measure (2) which is resulting in around 1500 scans.

I have attached a sample model that replicates the problem and I show the server timings from DAX Studio.  The first one is a stright sum of paid amount against agent names and runs fine, the second is the claim count against the same list of agents and you can see the multiple scans

Sum of paid amount works fine.Claim Count generating the many scans

I tried creating an agent only table and a bridge table that held the Agent_Group mapping then linking that to vGROUP but the result was the same performance.

Any help would be greatly appreciated.

[Mariusz]

Hi @jdbuchanan71 

Small suggestions:

Why not combine all dimensions ( vClient, vGroup, vGroupDivision and vGroupDivision ...)  it into one dimension ( vGroupDivision ) this would simplify the model and improve the performance and create a proper start schema.

Next, you can join vAgent M*M with vCLAIMS but not bidi anymore, vAgent propagating claims. as below

btw, nice to see you back John!

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[jdbuchanan71]

Thank you @Mariusz for the reply. 

I should have mentioned that the real model is running on an SSAS back end which does not support the many to many *:* relationships.  I didn't see a version of SSAS that does support *:*, do you know of one?

I also need vAGENT to filter three lower level tables (claims, invoices, enrollment) all of which link up to the GroupDivisionPlan so that is why I am coming in from that level.

If I make it a true star schema by denormalizing vCLIENT, vGROUP, vGROUPDIVISION and vGROUPDIVISIONPLAN I would not be able to hook agents in, again because of the many to many not being supported although I could do that with a bridge table.

One thing I am going to try is adding a claim_count_ID to the vCLAIM that uses the correct field (claim_ID or batch_ID) depending on the claim type.  That should cut the calls to the storage engine in half.

[jdbuchanan71 · Accepted solution]

Using information from this article https://www.sqlbi.com/articles/analyzing-distinctcount-performance-in-dax/

I was able to modify the measures and increase the speed of the visual by 225 X.

Old Count = DISTINCTCOUNT ( vCLAIM[Client_Claim_Type_ID] )

New Count = SUMX ( DISTINCT ( vCLAIM[Client_Claim_Type_ID] ), 1 )

[v-shex-msft]

HI @jdbuchanan71,

I think this should more relate to your data model relationship and KEEPFITLERS, probably you can take a look at following documents about AS tabular relationship and KEEPFITLERS function:

Using KEEPFILTERS in DAX 

Relationships in analysis services tabular models 

Regards,

Xiaoxin Sheng