DAX Doubt (bidrectionality and cardinality between some tables)

Ricardo77 — Mon, 15 Nov 2021 10:20:58 GMT

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.

Re: DAX Doubt (bidrectionality and cardinality between some tables)

PaulOlding — Mon, 15 Nov 2021 11:32:23 GMT

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-unplugged-37/

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.

topic Re: DAX Doubt (bidrectionality and cardinality between some tables) in DAX Commands and Tips

DAX Doubt (bidrectionality and cardinality between some tables)

Re: DAX Doubt (bidrectionality and cardinality between some tables)