Compare values from two tables and get count...

[ddpl]

Hello,

I have two tables,

First one is... Sales,

SaleID_	Product Type_	Customer_	Discount
1	A	Ram	15
2	B	Ram	16
3	B	Krishna	17
4	C	Krishna	18
5	D	Krishna	19
6	A	Ramesh	20
7	B	Ramesh	21
8	B	Ramesh	22
9	B	Ramesh	23
10	C	Kaplesh	24
11	C	Kaplesh	25
12	D	Kaplesh	26

and the second one is... Stock,

StockID_	Product Type_	Discount
1	A	10
2	A	11
3	A	12
4	A	13
5	A	14
6	B	15
7	B	16
8	B	17
9	B	18
10	B	19
11	C	20
12	C	21
13	C	22
14	C	23
15	C	24
16	D	25
17	D	26
18	D	27
19	D	28
20	D	29

Both tables are connected to [Product Type] column.

I want last column in below table, 

Count of Unique [StockID] with one condition that Stock [Discount] must be greater than Sold [Discount] 

Customer_	Total Sold Quantity_	Available Stock_	Actual Stock Quantity
Ram	2	10	0(A)+3(B) = 3
Krishna	3	15	2(B)+5(C)+5(D) = 12
Ramesh	4	15	0(A)+ 0(all 3 B types) = 0
Kalpesh	3	10	0(all 2 C type) + 3(D) = 3

I want the highlighted in Red colour values, extra details are just explaination.

[v-binbinyu-msft · Accepted solution]

Hi @ddpl ,

Please try below steps:

1. create a measure with below dax formula

Measure =
VAR cur_customer =
    SELECTEDVALUE ( Sales[Customer_] )
VAR tmp1 =
    SELECTCOLUMNS (
        Stock,
        "Stock ID", [StockID_],
        "Product Type", [Product Type_],
        "Count", [Discount]
    )
VAR tmp2 =
    CROSSJOIN ( Sales, tmp1 )
VAR tmp3 =
    CALCULATETABLE (
        VALUES ( Stock[StockID_] ),
        FILTER (
            tmp2,
            [Customer_] = cur_customer
                && [Product Type] = [Product Type_]
                && [Count] > [Discount]
        )
    )
VAR _val =
    COUNTROWS ( tmp3 )
RETURN
    IF ( ISBLANK ( _val ), 0, _val )

2. add a table visual  with field and measure 

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-binbinyu-msft · Accepted solution]

Hi @ddpl ,

Please try below steps:

1. create a measure with below dax formula

Measure =
VAR cur_customer =
    SELECTEDVALUE ( Sales[Customer_] )
VAR tmp1 =
    SELECTCOLUMNS (
        Stock,
        "Stock ID", [StockID_],
        "Product Type", [Product Type_],
        "Count", [Discount]
    )
VAR tmp2 =
    CROSSJOIN ( Sales, tmp1 )
VAR tmp3 =
    CALCULATETABLE (
        VALUES ( Stock[StockID_] ),
        FILTER (
            tmp2,
            [Customer_] = cur_customer
                && [Product Type] = [Product Type_]
                && [Count] > [Discount]
        )
    )
VAR _val =
    COUNTROWS ( tmp3 )
RETURN
    IF ( ISBLANK ( _val ), 0, _val )

2. add a table visual  with field and measure 

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ddpl · Accepted solution]

@v-binbinyu-msft ,

Thanks for the answer, it perfectly work on sample data.

but concern is about cross join, as per my data... cross join created around 9 billion rows and the figure did not show untill I sliced any single customer.

Anyway thanks again for the enlightenment.