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 IV
Super User IV

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
Highlighted
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 IV
Super User IV

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 IV
Super User IV

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors