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 Everyone, I'm trying to check if a customer code is found within a list of customers, I've created the list of customers using the following sytanx and it's working fine, the only issue I'm facing when I call the table within a calclated column.
This method works:
Growth Status =
IF (
'Customers'[Customer code]
in table
,"Positive Growth",BLANK())
table = CALCULATETABLE (
VALUES ( 'sales'[Customer code] ),
FILTER (
SUMMARIZECOLUMNS(
'sales'[Customer code],
FILTER (
'Calendar',
'Calendar'[Year] = YEAR ( TODAY () )
),
"YTD Growth", [% YTD Sales Vs. LY YTD Sales]
),
[YTD Growth] > 0
)
)
This method returns an errror "SummarizeColumns() may not be used in this context."
Growth Status =
IF (
'Customers'[Customer code]
in
CALCULATETABLE (
VALUES ( 'sales'[Customer code] ),
FILTER (
SUMMARIZECOLUMNS(
'sales'[Customer code],
FILTER (
'Calendar',
'Calendar'[Year] = YEAR ( TODAY () )
),
"YTD Growth", [% YTD Sales Vs. LY YTD Sales]
),
[YTD Growth] > 0
)
)
,"Positive Growth",BLANK())
I'm trying to use the second method as first method is not sufficient in the long run.
Solved! Go to Solution.
I managed to work it out with the following dax syntax, changed the summarizecolumns function to addcolumns function in conjunction with summrize function:
Growth Status =
IF (
'Customers'[Customer code]
IN CALCULATETABLE (
VALUES ( 'sales'[Customer code] ),
FILTER (
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( 'sales', 'sales'[Customer code] ),
"YTD", CALCULATE ( [% YTD Sales Vs. LY YTD Sales] )
),
FILTER (
'Calendar',
'Calendar'[Year] = YEAR ( TODAY () )
)
),
[YTD] > 0
)
),
"Positive Growth",BLANK())
I managed to work it out with the following dax syntax, changed the summarizecolumns function to addcolumns function in conjunction with summrize function:
Growth Status =
IF (
'Customers'[Customer code]
IN CALCULATETABLE (
VALUES ( 'sales'[Customer code] ),
FILTER (
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( 'sales', 'sales'[Customer code] ),
"YTD", CALCULATE ( [% YTD Sales Vs. LY YTD Sales] )
),
FILTER (
'Calendar',
'Calendar'[Year] = YEAR ( TODAY () )
)
),
[YTD] > 0
)
),
"Positive Growth",BLANK())
Hey @Anonymous ,
you can get the values that are in both tables with the INTERSECT function and VALUES:
MyIntersectTable =
INTERSECT (
VALUES ( 'Customers'[Customer code] ),
VALUES ( 'sales'[Customer code] )
)
Let me know if that works. If you need any help please let me know.
My issue is not with the intersection, it's with the summrizecolumn function
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 |
---|---|
112 | |
97 | |
83 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |