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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.