Filter factTable with two combinded columns

kmansweden2017 — Thu, 20 Oct 2022 13:03:34 GMT

Hi,

I am struggling and starting to think there isnt a straight forward way to filter this table the way i need.

I want to display and count the rows left after I filter out rows that has
"VehicleStockStatusCode" = 23 AND "TotalBookValue" = 0
combinded on the same rowcontext.

I tried below but that filters them seperatly, not as combind row

Vehicle Stock (Filtered) = CALCULATE( COUNTA(FactVehicleStock[Vehicle_KEY]), FILTER( FactVehicleStock, ( FactVehicleStock[TotalBookValue] <> 0 && FactVehicleStock[VehicleStockStatusCode_KEY] <> 60 ) //60 = 23 )

Result should be 18

VehicleNumber	VehicleStatusCode	TotalBookValue	NumberOfDaysInStock
76569	24	317937	505
77237	33	0	0
78325	24	373161	385
79076	24	-17983	341
79096	24	-25178	338
79203	24	10656	335
79319	24	-13683	327
79395	23	0	0
79456	23	0	0
79480	24	-19022	313
80080	23	0	0
80254	24	-15267	258
80257	24	39	258
80753	24	-15267	244
80774	24	47	243
80932	23	0	0
80933	23	0	0
80934	23	0	0
81080	23	0	0
81782	23	0	0
81960	23	0	0
82562	23	0	0
82564	23	0	0
82565	24	512	147
83044	24	0	112
83141	24	4201	103
83388	23	3249	0
83497	23	0	0
83510	24	0	65
83747	24	0	48
84251	24	0	19

Re: Filter factTable with two combinded columns

johnt75 — Thu, 20 Oct 2022 14:11:24 GMT

You could try

Vehicle stock ( filtered ) = VAR FilteredRows = CALCULATETABLE( VALUES( 'FactVehicleStock'[VehicleNumber] ), FactVehicleStock[TotalBookValue] = 0 && FactVehicleStock[VehicleStatusCode] = 23 ) RETURN CALCULATE( COUNTROWS('FactVehicleStock'), EXCEPT( VALUES('FactVehicleStock'[VehicleNumber]), FilteredRows) )

Re: Filter factTable with two combinded columns

kmansweden2017 — Fri, 21 Oct 2022 08:27:07 GMT

Thnx johnt75 it worked as expectet 👍

topic Re: Filter factTable with two combinded columns in DAX Commands and Tips

Filter factTable with two combinded columns

Re: Filter factTable with two combinded columns

Re: Filter factTable with two combinded columns