cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
carloscabreraq Frequent Visitor
Frequent 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
 
 
6 REPLIES 6
Super User
Super User

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 Frequent Visitor
Frequent 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
 
 
carloscabreraq Frequent Visitor
Frequent 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.