Calucaltion on Filtered set op Rows when Value is containing in A collumn

Question

Hello Guys,I am trying to get a measure (of calculated collumn) in my Power BI that returns a SUM of a specific value.To be more specific;&nbsp;The table i have is;Empl &nbsp;| Month | Value | YearA &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; 2016A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; 2016A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; 2016A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; 2016A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2016A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2016A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; 2016B &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; 2016B &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; 2016B &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp;2016B &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp;2016B &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp;2016I would like to calculate each Year/Month per Employee the Value, when The rows&nbsp;for the specific Year/Month/Employee is Containing at least one row with zero (0) then the result of the measure should be 0, else the result should be 1.In other words; the above table should result in&nbsp;Empl &nbsp;| Month | Measure Result&nbsp;| YearA &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2016A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2016A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2016 &lt;&lt;The rows of same&nbsp;year, Month, Employee is only containing the value 1 and no other rows with the value 0B &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2016&lt;&lt;The rows of this year, Month, Employee is only containing the value 1&nbsp;and no other rows with the value 0B &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2016Last but not least i would like to SUM the results Per year and Month on all employees.I realy hope someone could help me out on this matter, i would realy appreciate it!

v-haibl-msft · Accepted Answer

Z4m&nbsp;In this scenario, you can create two measures with following formulas to get the results.Measure Result = 
IF ( SUM ( Table1[Value] ) &lt; COUNTROWS ( Table1 ), 0, 1 )Measure Total = 
SUMX (
    SUMMARIZE (
        Table1,
        Table1[Empl],
        Table1[Month],
        Table1[Year],
        "Measure Result", [Measure Result]
    ),
    [Measure Result]
)&nbsp;Best Regards,Herbert&nbsp;

Forum Discussion

Calucaltion on Filtered set op Rows when Value is containing in A collumn

1 Reply

Related content

Contain Word

Filter contains

Visual calculation- Contain

Filter with Text Contains

filter table based on selected value using contains