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

[BrianNeedsHelp]

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

[techies]

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

[v-saisrao-msft]

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.

[BrianNeedsHelp]

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

[v-saisrao-msft · Accepted solution]

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.

[Jihwan_Kim]

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)

[BrianNeedsHelp]

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?