Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi guys,
I am unsure how to proceed DAX wise with my current small project, I'll try to keep it short :
I have a main sales table (t1) with all the sales, with all the generic codes
I have a second table that take thoses sale, with wich I have selected code,
And a final table that take some items that have special code on some occasion.
t1 :
Item 1 | Code1 | 3 |
item 1 | Code2 | 5 |
item 1 | Code3 | 1 |
Item 2 | Code1 | 8 |
item 2 | Code2 | 5 |
item 2 | Code3 | 18 |
t3.
item1 | Code4 | Sales from t1 |
Things is that the codes from T3 are dependant on the promotion, so item1 might get code4, where as item2 get code5.
Hence why I think making a table (t3) to have the item with the related promo code. The sales are still in t1, but I need to target them item by item for the additionnal code. (hope it make sense )
Any suggestion for a dax measure to get all sales from t1 generic code, + with the promo code ( from t3)
Solved! Go to Solution.
@VahidDM Thanks for your contribution on this thread.
Hi @amadnei ,
You can follow the steps below to get it, please find the details in the attachment.
1. Create the relationship between 'All sales' table and 'promotion code' table with the field 'All sales' [[ITEM_NAME]] and 'promotion code' [PROMO_ITEM_NAME]
2. Create a measure as below to get it:
SALE_FROM_CODE =
VAR _itemname =
SELECTEDVALUE ( 'All sales'[ITEM_NAME] )
RETURN
CALCULATE (
SUM ( 'All sales'[SALES] ),
FILTER (
'All sales',
'All sales'[ITEM_NAME] = _itemname
&& 'All sales'[SALE_CODE] IN VALUES ( 'promotion code'[PROMO_CODE] )
)
)
Best Regards
Hi, I can't post any work related data but I will make a small fake sheet to explain it better in the coming days if it help other understand.
That would be great!
Hi @amadnei
Can you post sample data as text with column names and expected output?
Little bit confused
please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar (Add Column names)
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables
Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/
Here is a mockup of my 2 main table, with the expected result
All sales table
ITEM_ID | ITEM_NAME | SALE_CODE | SALES |
1 | BLENDER | CODE1 | 4 |
1 | BLENDER | CODE1 | 52 |
1 | BLENDER | CODE2 | 5 |
1 | BLENDER | CODE3 | 12 |
1 | BLENDER | CODE4 | 56 |
2 | TABLE | CODE1 | 9 |
2 | TABLE | CODE3 | 23 |
2 | TABLE | CODE4 | 52 |
3 | KNIFE | CODE1 | 12 |
3 | KNIFE | CODE2 | 25 |
3 | KNIFE | CODE3 | 63 |
3 | KNIFE | CODE3 | 12 |
3 | KNIFE | CODE4 | 7 |
promotion code
PROMO_ITEM_ID | PROMO_ITEM_NAME | PROMO_CODE |
1 | BLENDER | CODE3 |
1 | BLENDER | CODE4 |
2 | KNIFE | CODE3 |
EXPECTED_RESULT | |
ITEM_NAME | SALE_FROM_CODE |
BLENDER | 68 |
KNIFE | 75 |
Note that some code from the 2nd table are available on other item that are not in the promo table, but I can't count those in. It's because sometime promo code are used as regular code.
I guess the basic explaination is : IF code + item name are in 1st table, get those sales.
I tried a simple calculate(filter(all(table1) code = code & name = name, but doesn't work properly and take ages to load.
Also tried to use IN function, work very well for few code, but not sure how to add a switch function to change code dependant on the item name.
Hope it's clearer ! thanks 🙂
@VahidDM Thanks for your contribution on this thread.
Hi @amadnei ,
You can follow the steps below to get it, please find the details in the attachment.
1. Create the relationship between 'All sales' table and 'promotion code' table with the field 'All sales' [[ITEM_NAME]] and 'promotion code' [PROMO_ITEM_NAME]
2. Create a measure as below to get it:
SALE_FROM_CODE =
VAR _itemname =
SELECTEDVALUE ( 'All sales'[ITEM_NAME] )
RETURN
CALCULATE (
SUM ( 'All sales'[SALES] ),
FILTER (
'All sales',
'All sales'[ITEM_NAME] = _itemname
&& 'All sales'[SALE_CODE] IN VALUES ( 'promotion code'[PROMO_CODE] )
)
)
Best Regards
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |