cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
 
 
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
 
 
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.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 413 members 4,127 guests
Please welcome our newest community members: