Forum Discussion

douglas-verdum's avatar
douglas-verdum
New Member
4 years ago
Solved

Counting based on another field

Hi!   I would like to create a measure that is able to count the number of Matriculas in which:   1. the IdMotivo is equal to 1 and 2. there is a IdNatureza equals to 4 whatever the date it occu...
Need Help
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    douglas-verdum So generally you would have a relationship between your date table and that table based on date and you would use Month or something like that from your date table in the axis of your visual. Also, if the = 4 part can be in any month, you would need a slight variation:

    Measure = 
  VAR __Table = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'), [IdNatureza] = 4), "__Matricula", [Matricula]))
  VAR __Table1 = DISTINCT(SELECTCOLUMNS(FILTER('Table', [IdMotivo] = 1), "__Matricula", [Matricula]))
RETURN
  COUNTROWS(INTERSECT(__Table1, __Table))
douglas-verdum's avatar
douglas-verdum
New Member
4 years ago

great Greg_Deckler 

 

but this gives the aggregate value

 

could you come up with something that splits this number along the date filter context in a view?

there is a dimCalendar related to this fact table

 

thanks!

Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

douglas-verdum So generally you would have a relationship between your date table and that table based on date and you would use Month or something like that from your date table in the axis of your visual. Also, if the = 4 part can be in any month, you would need a slight variation:

Measure = 
  VAR __Table = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'), [IdNatureza] = 4), "__Matricula", [Matricula]))
  VAR __Table1 = DISTINCT(SELECTCOLUMNS(FILTER('Table', [IdMotivo] = 1), "__Matricula", [Matricula]))
RETURN
  COUNTROWS(INTERSECT(__Table1, __Table))