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.
Hi All,
Good Day!
I've been trying to solve this issue for days. Need your help on how to resolve this problem.
I have 3 sources of table
SSD table
Customer Grouping | Product Grouping |
Restaurant A | SKU A |
Restaurant B | SKU B |
Restaurant C | SKU C |
Restaurant D | SKU D |
Restaurnt E | SKU E |
PSD table
Customer Grouping | Product Grouping |
Restaurant X | SKU A |
Restaurant B | SKU B |
Restaurant C | SKU C |
Restaurant D | SKU D |
Restaurant Z | SKU F |
As you can see SSD and PSD table have almost the same customers the difference is these two come from different source
Both PSD AND PSD have dates which is connected to my calendar table
Outlet Table (From excel file
Customer Grouping | Outlet # |
Restaurant X | 1 |
Restaurant B | 2 |
Restaurant C | 2 |
Restaurant D | 2 |
Restaurant Z | 1 |
Restaurant A | 1 |
1 |
These are the table from different sources, what I need to do are the following
1. Get distinct count of Grouping customer of SSD and PSD for the past 6 months.
(I already have a DAX calculating the distinct count of Grouping customer SSD and PSD . After getting the Distinct count of grouping customer of ssd and psd for the past 6 months.)
2. I need to map it to Outlet Table Customer grouping column to get the sum of outlet #.
( This is the part that I couldnt solve would appreciate so much if you could help me.)
I have been thinking of trying another approach which is creating a new table to get the psd and ssd distinct data and use it to map to customer grouping. Instead of creating a DAX.
Solved! Go to Solution.
Hi @Anonymous ,
You can create intermediate calculated table like DAX below, then create relationships with your three original tables with "Both" of Cross filter direction.
Distinct Customer Grouping= UNION(DISTINCT('SSD'[Customer Grouping]), DISTINCT('PSD'[Customer Grouping]),DISTINCT('Outlet'[Customer Grouping]))
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.
Best regards
Amy
Hi @Anonymous ,
You can create intermediate calculated table like DAX below, then create relationships with your three original tables with "Both" of Cross filter direction.
Distinct Customer Grouping= UNION(DISTINCT('SSD'[Customer Grouping]), DISTINCT('PSD'[Customer Grouping]),DISTINCT('Outlet'[Customer Grouping]))
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The table route may be the way to go. Would still be DAX.
I am going to assume that you have the DAX for the distinct customers, you could do this:
Measure =
VAR __Customers = [Distinct Customers] //this is your measure
VAR __Table =
ADDCOLUMNS(
'Outlet Table',
"Final",IF([Customer Grouping] IN __Customers,[Outlet #],0)
)
RETURN
SUMX(__Table,[Final])
Hi @Greg_Deckler,
Thank you for replying. I tried the measure you suggested. its giving me this error.
__Customers is supposed to be the distinct list of customers that you said you had, not their count, so something like:
VAR __Customers = DISTINCT('Table'[Column])
This returns a table of values, which is what you want for the measure.
Hello @Greg_Deckler
My apologies, the name of the measure is wrong. This is the measure I created if it expands. As you can see the measure is trying to get values other than psd and ssd during the last 6 months with condition, but it does not allow me since it says that it is not a valid table
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |