Hi,
I have many tables and I would like to find out the best way to count the number of units sold that meet a criteria.
In tblInvMaster I have a fields called PROMO CODE. I want to sum up the number of units from tblSALE that have a PROMO CODE.
So I can use the related function in DAX:
=IF(RELATED(tblInvMaster[PROMO PRICE])>0,'SALES TABLE'[UNITS SOLD],0)
and then total the column. But I don't think it efficient as it would have to look through 1m rows.
I was trying to write this as a measure:
PROMO UNITS SOLD:=SUMX (
ADDCOLUMNS (
SUMMARIZE (
'SALES TABLE',
"PROMO UNITS",'tblInvMaster'[PROMO PRICE],
"NetUnitsSold", SUM ( 'SALES TABLE'[UNITS SOLD] )
),
"Promo Units Sold", IF ( [PROMO UNITS] > 0, [NetUnitsSold], 0 ) )
),
[Promo Units Sold]
)
But it didn't work.
Thoughts?
I have many tables and I would like to find out the best way to count the number of units sold that meet a criteria.
In tblInvMaster I have a fields called PROMO CODE. I want to sum up the number of units from tblSALE that have a PROMO CODE.
So I can use the related function in DAX:
=IF(RELATED(tblInvMaster[PROMO PRICE])>0,'SALES TABLE'[UNITS SOLD],0)
and then total the column. But I don't think it efficient as it would have to look through 1m rows.
I was trying to write this as a measure:
PROMO UNITS SOLD:=SUMX (
ADDCOLUMNS (
SUMMARIZE (
'SALES TABLE',
"PROMO UNITS",'tblInvMaster'[PROMO PRICE],
"NetUnitsSold", SUM ( 'SALES TABLE'[UNITS SOLD] )
),
"Promo Units Sold", IF ( [PROMO UNITS] > 0, [NetUnitsSold], 0 ) )
),
[Promo Units Sold]
)
But it didn't work.
Thoughts?