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

[carloscabreraq]

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.

[carloscabreraq]

@v-piga-msft @LivioLanzo

This this the data example, Test.pbi. Thanks

[Ashish_Mathur · Accepted solution]

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.

[carloscabreraq]

Thanks @Ashish_Mathur

[Ashish_Mathur]

You are welcome.

[v-piga-msft]

Hi @carloscabreraq,

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

Best  Regards,

Cherry

[LivioLanzo]

Hello @carloscabreraq

are you able to show a sample of your Data?