jeudi 16 mai 2019

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.

Aucun commentaire:

Enregistrer un commentaire