Forum Discussion

mcinnisbr's avatar
mcinnisbr
Icon for Advocate I rankAdvocate I
6 years ago
Solved

Returning count if value contains specific group

Hi, I have a problem in returning proper facility counts based on a strange grouping in my facility type data column. My data look like this.     Facility         Type Facility A COED ...
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi mcinnisbr 

     

    Can you try the following:

     

    Assume that Table1 is the table you created with the columns Facility and Type.

     

    1. Create a new table (Table2) with the following DAX code:

    Column1 = Distinct('Table1'[Facility])

     

    2. In this new table, create a second column with the following DAX code:

    Column2 = LOOKUPVALUE('Table1'[Type], 'Table1'[Facility], 'Table2'[Column1], "COED")

     

    You should have a table of distinct facilities and a single type.

     

    Hope this helps!

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

You could create a column: 

Column = 
  VAR __Table = FILTER('Table',[Facility] = EARLIER('Table'[Facility])
RETURN
  IF(COUNTROWS(__Table = 1,[Type],"COED")