topic Re: If any column in Row has 0, True, Else False in DAX Commands and Tips

If any column in Row has 0, True, Else False

BrianNeedsHelp — Thu, 28 Aug 2025 23:40:42 GMT

All of the Device Names are in one column called DeviceModel.

I have a table like this where I'm wanting to add one column-'Column Needed' based on if there are any zeros or blanks in each row. Goal is to be able to filter the one column to True. Thx!

Location	Device1	Device2	Device3	Column Needed
A	1	0	2	TRUE
B	3	4	5	FALSE
C	0	3	3	TRUE
D	1	3	0	TRUE

Everything I've tried only calculates each column individually. E.g.

ContainsZero = var point_sale = SUMX(VALUES('Hierarchy'[Location]) ,[DAX Product Count]) return IF(MINX(CALCULATETABLE(SUMMARIZE('IOH',IOH[DeviceModel]),ALLEXCEPT('Hierarchy','Hierarchy'[Location])),point_sale)=0,"true","false")

Re: If any column in Row has 0, True, Else False

Jihwan_Kim — Fri, 29 Aug 2025 03:04:36 GMT

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I tried to not create calculated column, but I tried to create calculated measures, and then I inserted into the visual level filter pane to filter the visualization.

DAX Product Count: = VAR _result = SUM ( IOH[ProductCount] ) VAR _t = ADDCOLUMNS ( FILTER ( ALL ( DeviceModel[DeviceModel], DeviceModel[Sort] ), DeviceModel[DeviceModel] <> "Needed" ), "@result", CALCULATE ( SUM ( IOH[ProductCount] ) ) ) RETURN SWITCH ( SELECTEDVALUE ( DeviceModel[DeviceModel] ), "Needed", PRODUCTX ( _t, [@result] ) = 0, _result )

Visual_Filter: = VAR _t = ADDCOLUMNS ( FILTER ( ALL ( DeviceModel[DeviceModel], DeviceModel[Sort] ), DeviceModel[DeviceModel] <> "Needed" ), "@result", CALCULATE ( SUM ( IOH[ProductCount] ) ) ) RETURN IF(PRODUCTX(_t, [@result]) = 0,1)

Re: If any column in Row has 0, True, Else False

v-saisrao-msft — Fri, 29 Aug 2025 09:08:56 GMT

Hi BrianNeedsHelp,

Thank you Jihwan_Kim, for your insights.

I was able to replicate your issue with the sample data you provided. Here is the output I obtained after adding a calculated column, and I’ve attached the PBIX file for you to review.

Column Needed = IF ( ( 'Table'[Device1] = 0 || ISBLANK ( 'Table'[Device1] ) ) || ( 'Table'[Device2] = 0 || ISBLANK ( 'Table'[Device2] ) ) || ( 'Table'[Device3] = 0 || ISBLANK ( 'Table'[Device3] ) ), TRUE (), FALSE () )

Hope this helps

Thank you.

Re: If any column in Row has 0, True, Else False

BrianNeedsHelp — Fri, 29 Aug 2025 20:13:07 GMT

Hi Jihwan_Kim . The tables I have are just Hierarchy and IOH. I don't have the DeviceModel table, which has Sort. My model looks like yours except for the DeviceModel table. Do I need another table to make it work?

Re: If any column in Row has 0, True, Else False

techies — Fri, 29 Aug 2025 19:25:05 GMT

Hi BrianNeedsHelp Or in power query editor , pls try custom column

if List.Contains({0, null}, List.Min(Record.ToList(Record.SelectFields(_, {"Device1","Device2","Device3"}))))
then true
else false

Re: If any column in Row has 0, True, Else False

BrianNeedsHelp — Fri, 29 Aug 2025 20:03:14 GMT

Hi v-saisrao-msft There is no [Device 1], [Device 2] etc. There is only one column that has all of the devices-called DeviceModel. So to get the devices to show up in columns I had to do a Matrix. I tried using like this and it puts false in all of the rows, and it puts them in the Location field.

ColChecker = IF ( ( 'IOH'[DeviceCount] = 0 || ISBLANK ( IOH[DeviceCount] ) ), TRUE (), FALSE () )

Re: If any column in Row has 0, True, Else False

v-saisrao-msft — Mon, 01 Sep 2025 06:29:23 GMT

Hi BrianNeedsHelp,

Thank you for the clarification. Because your data uses a long format with a single DeviceModel column containing counts, you'll need to review all devices for each Location. You can use the following calculated column:

Column Needed = VAR ZeroCheck = CALCULATE ( COUNTROWS ( FILTER ( IOH, IOH[DeviceCount] = 0 || ISBLANK ( IOH[DeviceCount] ) ) ), ALLEXCEPT ( IOH, IOH[Location] ) ) RETURN IF ( ZeroCheck > 0, TRUE, FALSE )

Hope this helps.

Thank you.