CODEWITHSUNDEEP
if-statementgroup-bypowerbidax
Fiona
Fiona

Reputation: 477

Flag if value occurs more than once per month - group by if statement in DAX PowerBI

I have the following example data.

 YYYY_MM    Reg    Test
 2019_01    ABC     1
 2019_01    ABC     0
 2019_01    DFG     0
 2019_02    ABC     0
 2019_02    ABC     0
 2019_02    DFG     0
 2019_02    DFG     1
 2019_02    DFG     1
 2019_02    DFG     0
 2019_02    HIJ     0

I want to flag and Reg within each unique YYYY_MM where test = 1 one or more times that month. Hence the final data set would look as follows. 

 YYYY_MM    Reg    Test    Flag
 2019_01    ABC     1       1
 2019_01    ABC     0       1
 2019_01    DFG     0       0
 2019_02    ABC     0       0
 2019_02    ABC     0       0
 2019_02    DFG     0       1
 2019_02    DFG     1       1 
 2019_02    DFG     1       1
 2019_02    DFG     0       1
 2019_02    HIJ     0       0

I was thinking of using the GROUPBY function or SUMMARIZE but am not totally sure how to use them for what I need it to do. Happy to go in a totally different direction though.

Upvotes: 1

Views: 467

Answers (1)

RADO
RADO

Reputation: 8148

I will assume that you need this flag as a calculated column, and that your table name is "Data".

Create a column:

Flag =
VAR Total_Test =
    CALCULATE ( SUM ( Data[Test] ), ALLEXCEPT ( Data, Data[YYYY_MM], Data[Reg] ) )
RETURN
    IF ( Total_Test > 0, 1, 0 )

Result:

enter image description here

How it works: We use ALLEXCEPT to group data. We sum up all values for column test, but keep filters on columns yyyy-mm and Reg.Store result in a variable, and then perform a logical test.

Upvotes: 1

Related Questions