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
Highlighted
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
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

Highlighted
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.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors