CONCATENATE not working in SUMMARIZE

[kush23456]

I have sample data below:

ID	Source	Date
123	Source1	01/08/2022
123	Source1	01/09/2022
123	Source2	01/07/2022
456	Source1	01/10/2022
789	Source1	01/01/2022
101	Source1	01/03/2022
102	Source1	02/04/2022
103	Source1	08/12/2022
103	Source2	10/02/2022

I would like to count the ID's which ONLY have Source as Source1 before a selected date, e.g. 31/08/2022.

First, to filter the table down to Date <= 31/08/2022 I should get: 

ID	Source	Date
123	Source1	01/08/2022
123	Source2	01/07/2022
789	Source1	01/01/2022
101	Source1	01/03/2022
102	Source1	02/04/2022
103	Source2	10/02/2022

Then, I need to check whether an ID only ever had a source as Source1. My thoughts are a concatenate (however, I would welcome a different way of doing this) which will result in: 

ID	Concat_Source
123	Source1, Source2
789	Source2
101	Source1
102	Source1
103	Source2

Finally, I want to check whether the concat_source ever contained Source 2. If it did, then I don't want to count it. In this case, only 101,102 would be counted so the result is 2 shown on a card visual.

I have been able to recreate the above logic with the following: 

VAR lastdateselected = LASTDATE ( Date[Dates] ) //assume this is 31/08/2022 and comes from a date table. 

VAR sourcetbl1 =

CALCULATETABLE(

SUMMARIZE (

    ALL (Table1),

            Table1[ID],

            Table1[Date],

             Table1[Source]))

VAR datetablefilter =

CALCULATETABLE(

SUMMARIZE (

        FILTER(sourcetbl1, Table1[Date] <= lastdateselected && Table1[ID] <> ""),

            Table1[ID],

            Table1[Date],

            Table1[Source]

           ))

VAR concattbl =

CALCULATETABLE(

SUMMARIZE(datetablefilter,

Table1[ID],

"Concat", CALCULATE(CONCATENATEX(Table1, Table1[Source], ", "))))

VAR filter1 = CALCULATETABLE(FILTER ( concattbl, NOT(CONTAINSSTRING([Concat], "Source 2" ) ) ) )

RETURN

COUNTROWS (filter1)

The complexity is that I have a date slicer on my dashboard. A user may select a period of data, e.g. 01/08/2022 - 31/08/2022. Only the last date selected is relevant for the above calcs which is why I created the var sourcetbl1 with the ALL statement but it doesn't seem to work.

The formulas I have above are working correctly when I add them as a new table and reference var 'filter1'. They are not working when I have them on the dashboard as a measure, like above. I am guessing it's to do with the date slicer interfering but I am not sure why or how to modify it? 

Please note - I do not want to disconnect my date table. 

[v-yueyunzh-msft · Accepted solution]

Hi , @kush23456 

According to your description, you want to get the count of the ID which date <= max slicer date you selected && only have "Source" . And the date table has a relationship between your tables, like this:

For your need , we can create two measures:

Measure = var _max_date_slicer = MAX('Date'[Date])
var _cur_id =  MAX('Table'[ID])
var _t = FILTER( ALL('Table') ,'Table'[ID]=_cur_id && 'Table'[Date] <= _max_date_slicer)
return
CONCATENATEX(_t ,[Source],",")

Count = var _max_slicer= MAX('Date'[Date])
var _t = FILTER( ALL('Table') ,'Table'[Date]<=_max_slicer)
var _t2 = ADDCOLUMNS(_t , "flag" , var _id = [ID]  var _source_count = COUNTROWS( FILTER(_t,[ID]=_id)) var _source =[Source] return IF(_source_count=1 && _source = "Source1",1,0))
return
SUMX(_t2,[flag])

Then we can put the fields on the visual and we can get your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[kush23456]

Thanks Aniya. 

Unfortunately, the solution didn't work as expected as there was a little added complexity in the sense that your measure looks at where a column = Source1. The measure I was looking for was whether it contained Source1. Easily adaptable but still didn't work quite as expected. However, your answer gave me some inspiration and I particularly like the SUMX on the 1's and 0's that you suggested.

I managed to get to a solution through the use of virtual tables, summarize, filter, remove filters (to remove my dates filter and give me more control than using an ALL statement) and taking a slightly different approach to the logic of working this out than I originally did. 

Thanks again for your valuable response!

[amitchandak]

@kush23456 , Use date in before of after slicer and try a measure like

Measure = var _tab = SUMMARIZE(FILTER(Table1, Table1[Source] = "Source1"), Table1[ID])

return

CONCATENATEX(SUMMARIZE(Filter(Table1, Table1[ID] in _tab && [Date]), [Source]), [Source])