cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
carloscabreraq Regular Visitor
Regular Visitor

Dax Quetion, "n to n" DAX and data modeling

Hi,

i have 2 Fact Tables "Sale" and "Cto", one "Sale" can be related with many "Cto"  and one "Cto" can be related with many "Sale", they are related by the Key "Doc_ID". I made a bridge table "Bridge_Doc", now i want to obtain wich "CTO is not in a "Sale", using a filter or a measure, can you help me?

 

Thanks

 

Regards.

 

Modelo.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Dax Quetion, "n to n" DAX and data modeling

Hi,

 

Here's what i did.  In the CTO Table, i wrote this calculated column formula

 

Present in Cto = LOOKUPVALUE(Cto[Doc_ID],Cto[Cto_ID],[Doc_ID])
 
I then click on the card visual and dragged the following measure there
 
Not present in CTO = CALCULATE(DISTINCTCOUNT(Sale[Doc_ID]),FILTER(Sale,NOT(ISBLANK(Sale[Doc_ID]))))
 
The result of the above measure is 229K.  To know which these 229K Doc ID's are, i created a Table visual and dragged the [Present in Cto] measure in that.  I then filtered the measure to show only blanks.  In this Table visual, i dragged Doc_ID from the Bridge_Doc Table.
 
Hope this helps.
 
Untitled.png
 
 

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
LivioLanzo Super Contributor
Super Contributor

Re: Dax Quetion, "n to n" DAX and data modeling

Hello @carloscabreraq

 

are you able to show a sample of your Data?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Community Support Team
Community Support Team

Re: Dax Quetion, "n to n" DAX and data modeling

Hi @carloscabreraq,

 

To get the solution quickly, please share some data sample which could reproduce your scenario and your desired output.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
carloscabreraq Regular Visitor
Regular Visitor

Re: Dax Quetion, "n to n" DAX and data modeling

@v-piga-msft @LivioLanzo

 

This this the data example, Test.pbi. Thanks

Super User
Super User

Re: Dax Quetion, "n to n" DAX and data modeling

Hi,

 

Here's what i did.  In the CTO Table, i wrote this calculated column formula

 

Present in Cto = LOOKUPVALUE(Cto[Doc_ID],Cto[Cto_ID],[Doc_ID])
 
I then click on the card visual and dragged the following measure there
 
Not present in CTO = CALCULATE(DISTINCTCOUNT(Sale[Doc_ID]),FILTER(Sale,NOT(ISBLANK(Sale[Doc_ID]))))
 
The result of the above measure is 229K.  To know which these 229K Doc ID's are, i created a Table visual and dragged the [Present in Cto] measure in that.  I then filtered the measure to show only blanks.  In this Table visual, i dragged Doc_ID from the Bridge_Doc Table.
 
Hope this helps.
 
Untitled.png
 
 

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

carloscabreraq Regular Visitor
Regular Visitor

Re: Dax Quetion, "n to n" DAX and data modeling

Thanks @Ashish_Mathur

Super User
Super User

Re: Dax Quetion, "n to n" DAX and data modeling

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)