Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, 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 highest 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 videos or webcasts that allows us to understand these type of DAX calculations.
Thanks in advance.
Hi @Ricardo77
SQL BI had a video this weekend on a scenario/model that seems very similar to this: https://www.sqlbi.com/tv/differences-between-generate-and-crossjoin-solving-business-scenarios-unplu...
They used a SUMMARIZE of the AD table to move the filter to Bridge1. On your diagram you have the arrows going both directions but I'm assuming your relationships are all single-direction. If that's not the case you should look to have the single-direction: bi-directional relationships can be a cause of slow queries.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |