Slow calculation (bidirectional relationships and more...)

DAX DAX Calculations
1

Capture
Capture1302×588 13 KB

Hello, this is my 1st topic, and for today due to confidentiality i don’t have a PBIX neither data to send, but i think i have the MOC and the right information to share.

I’m trying to create a table that shows the Client that has the biggest number of Hostnames (in table Win). I want to do something similiar in table SQL but i’ll procede with the Win example.
Simply placing the email and a measure, this is painfully slow, like 50 minutes…

Test = CALCULATE(DISTINCTCOUNT(‘Win’[Hostname]),
CROSSFILTER(AD[Key_Not_Showing_onImage], DoesntHelp[key1], None),
CROSSFILTER(Bridge1[key_AD], DoesntHelp[key2] , None),
)

I was trying to do a SUMMARIZE, like this, but it doesn’t work, due to the fact that the relation between AD and Bridge1 is n to 1. Notice the red box.

Test = CALCULATE(
DISTINCTCOUNT(‘Win’[Hostname]),
SUMMARIZE(Client[email],AD[keyAD],Bridge1[key_AD],Win[key_AD]
CROSSFILTER(AD[Key_Not_Showing_onImage], DoesntHelp[key1], None),
CROSSFILTER(Bridge1[key_AD], DoesntHelp[key2] , None),

)

So, if i create a filter in Client, like in column Company (not shown), it is relatively quick.
Ideally, I would like to get the clients with the highest number of Win Hostnames, of all companies (not filtered by company).
How can i do this calculation be quicker? In a measure or in a “virtual table”? If it is in a virtual table how can i show it in Power BI?

I would love if someone can help me out, and also referring to me what are the trainings here on E-DNA that allows us to understand these type of DAX calculations.

Thanks in advance.

2

Hi @lrmmf.

To help us further analyze your current state and visualize your issue, could you please provide as many as you can of:

  • Your work-in-progress PBIX file, using sanitized data if necessary
  • Your dataset as an Excel file (again, sanitized if necessary)
  • A detailed mock-up (marked-up screenshot of the visual in question or Excel file) showing your desired outcome.

Also, if you provide DAX or Power Query code in your post, please format it using the built-in formatter.

Greg

_eDNA Forum - Format DAX or PQ

3

Welcome to the forum @lrmmf :slight_smile:

We’ve noticed that no response has been received from you since November 15.

We are waiting for the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Here are some videos that may help you masking sensitive data and create datasets and data models representative of your problem:

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

4

Hello @lrmmf, due to your inactivity, we’ll be tagging this post as Solved.

Should you wish to add your masked demo pbix file and other supporting links and details, you can reopen this thread.