Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
kmansweden2017
Frequent Visitor

Filter factTable with two combinded columns

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

kmansweden2017_0-1666270904056.png

 

VehicleNumberVehicleStatusCodeTotalBookValueNumberOfDaysInStock  
7656924317937505  
772373300  
7832524373161385  
7907624-17983341  
7909624-25178338  
792032410656335  
7931924-13683327  
793952300  
794562300  
7948024-19022313  
800802300  
8025424-15267258  
802572439258  
8075324-15267244  
807742447243  
809322300  
809332300  
809342300  
810802300  
817822300  
819602300  
825622300  
825642300  
8256524512147  
83044240112  
83141244201103  
833882332490  
834972300  
8351024065  
8374724048  
8425124019  
1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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)
)

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

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)
)

Thnx johnt75 it worked as expectet 👍

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.