Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |