Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count column if it contains table value

I have a large body of data from deals created in our system. Each deal can have any number of labels recorded against it, and this shows up in one singuar column in our data extract e.g. Source: Website, Lender: CBA & Type: Commercial). These labels are displayed in the data in the order they were added to the deal, so the label types can be in any position of the column.

 

I need to report on the source of the deals but cannot figure out the best way to do this.

 

I have created a table that includes all of the 'Source' labels but now am having trouble figuring out the DAX measure to link this to the column in the main table.

 

I basically want a measure that will loop through the Source table to search for matching value in the Labels column of the main data set, then count that row so I can report on the number and value. Originally I thought something like this would work:

 

Source = CALCULATE(FIRSTNONBLANK('SourceTable'[Labels], 1), FILTER(ALL('SourceTabe'), SourceTable[Labels] = 'MainTable'[Labels]))
 
But this won't account for the fact the Source can be located anywhere in that column.
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi, Anonymous ;

    Please try to create a measure:

    Source = 
VAR _A =SELECTCOLUMNS ( 'MainTable', "Label", [Labels] )
VAR _B =SELECTCOLUMNS ( 'SourceTable', "Label", [Labels] )
VAR _c =INTERSECT ( _B, _A )
RETURN  COUNTROWS ( _c )

    The final output is shown below:

    According to my understand , Source' Lables column values A and B are included in Lables column of Main table, so count A and B in Source.

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , if you need a need column in MainTabletable

    Source = Minx(FILTER(('SourceTabe'), SourceTable[Labels] = 'MainTable'[Labels] && not(isblank('SourceTable'[Labels])) ,'SourceTable'[Labels])

     

     

    If you  need a measure

     

     

    Source = CALCULATE(FIRSTNONBLANK('SourceTable'[Labels], 1), FILTER(ALL('SourceTabe'), SourceTable[Labels] in values('MainTable'[Labels])))

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    Hi, Anonymous ;

    Please try to create a measure:

    Source = 
VAR _A =SELECTCOLUMNS ( 'MainTable', "Label", [Labels] )
VAR _B =SELECTCOLUMNS ( 'SourceTable', "Label", [Labels] )
VAR _c =INTERSECT ( _B, _A )
RETURN  COUNTROWS ( _c )

    The final output is shown below:

    According to my understand , Source' Lables column values A and B are included in Lables column of Main table, so count A and B in Source.

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.