How to count numbers based on related tables(tow tables)

[FrankNY007]

Hello, 

I have three tables, there is a simple structure:

Main table is B.

so i want to count how many shipment id in table b that have been marked as "Y" in C, but not beed marked as EDR in table A(has no record in table A).

in the screenshot, you can see shipment id 2, although it has reconrd in C, but since it has reconrd in A, so it won't be count 

like count(b.shipment.id) where(c.early_flag="Y" and isubll(b.munu_option))

[Seward12533]

As long as you have those relationship you can either just build a matrix or other visual of a measure that counts shipments from table B adn with Early Menu_Option and EarlyFlag in teh colums or filtered on the visual

Or write a DAX Measure using CALCULATE function with FILTER

Number Shipped Not EDR = CALCULATE(DISTINCTCOUNT(B[Shipment ID]),FILTER(C,C[Early Flag])="Y"),FILTER(A,A[Menu_Option]<>"EDR"))

[FrankNY007]

i got null.

it is becasue of inner join?

the table C supposed have more records than A

[drewlewis15]

Count = CALCULATE(DISTINCTCOUNT('Table B'[Shipmend ID]), 'Table A'[Menu_Option] <> "EDR", 'Table C'[Early Flag] = "Y")

[FrankNY007]

it gives me null

Early = CALCULATE(DISTINCTCOUNT('Order'[SALE_ID]),Airclic[EARLY_FLAG]="Y",EDR[MENU_OPTION]<>"EDR")

it is becaue of inner join?

[Seward12533]

@FrankNY007 Did you try with the explicit FILTER function in the calculate?

Number Shipped Not EDR = CALCULATE(DISTINCTCOUNT(B[Shipment ID]),FILTER(C,C[Early Flag])="Y"),FILTER(A,A[Menu_Option]<>"EDR"))

[FrankNY007]

i found a way to fix the probem, but don't know why.

the problem is table A may have mutiple EDR for same shipment ID. so B to A is 1:M relationship.

when you put A.Menu_Option and B.Shipment ID in a same table, It only give you B.shipment ID that has the reocrd in table A, so all the non-record ID is disappread.(seems like inner join)

BUT B and C is 1:1, so when you Put B. Shipment ID with C.Early_Flag.

it give all every ID, even it doen't marked as Y in table C

[Seward12533]

Is it working or not?  There are ways to handle it but woudl need more information.  

TIP if the DAX gets too messy or confusing you could build your own summary tables dynamically using DAX and then relate them.

To create an bridge table for ShipmentIDs

ShipmentIDs = VALUES(A[Shipment_ID])

Then you could create a Summary table of Shipment_IDs with counts for each menu option (or just EDR)

A_EDR = CALCULATE(SUMMARIZE(A,[ShipmentID],"Count",COUNTROWS(A)),A[MENU_Option]="EDR")

You could also do this in power query by creating a new query REFERENCED from the one use to generate A and then Pivot it in Power Query so you end up with a 1:1 table.

Once you have summary table(s) and bridge tables you can relate them in yoru model and do calculations.

[FrankNY007]

i put B.shiment ID, A.Menu_Option, C. Early_Flag

it give me a table only for ID both has menu_option and early_flag, becasue it default i inner join? 

when you filter the table by menu_option<>"EDR", it returns blank table.

[FrankNY007]

Stilll no.

think about is you only use 

Early = CALCULATE(DISTINCTCOUNT('Order'[SALE_ID]),EDR[MENU_OPTION]="EDR",Airclic[EARLY_FLAG]="Y")

it give me 54, this is all shipment that both EDR and Early_Flag="Y"

the problem is:

in the table for EDR, my query only give me all shipment has EDR, when you filter EDR[MENU_OPTION]<>"EDR", you got a empty table!