Convert Comma separated dax measure into a list

[sivarajan21]

Hi,

I have created a measure that gives dates in a comma separated format in a matrix visual(shows issue in below screenshot)

But I am confused how to get these dates in a list(one date per row). my expected output in visual must be as below:

Date

01/04/2022

02/04/2022

03/04/2022

04/04/2022

05/04/2022

06/04/2022

07/04/2022

08/04/2022

09/04/2022

The dax measure I had used is below:

dates =
CONCATENATEX (
    FILTER ( 'Calendar', NOT ( 'Calendar'[Date] IN VALUES ( Data[Date] ) ) ),
    FORMAT ( 'Calendar'[Date], "dd/mm/yyyy" ),
    ", ",
    'Calendar'[Date]

)

My sample data is as below:

Data(Date)

Data Date

04/01/2022 00:00

04/02/2022 00:00

04/07/2022 00:00

04/08/2022 00:00

04/09/2022 00:00

04/10/2022 00:00

04/11/2022 00:00

Calendar date 

Date

01/04/2022

02/04/2022

03/04/2022

04/04/2022

05/04/2022

06/04/2022

Please let me know if you need further info

Thanks in advance

[Ashish_Mathur]

Hi,

You can get multiple rows in the same cell.  In the CONCATENATEX() function, replace the "," with UNICHAR(10)

[sivarajan21]

@Ashish_Mathur 

Hi Ashish,

Apologise for the delay. I was working on this and testing it.

Brilliant! exactly the solution what I was looking for. 

I couldn't imagine it was so easy. currently these dates are in virtual format(result of measure-scalar):

Just to confirm, is it possible to get these missing dates in a summarize kind of table. so that I can use missing dates in a different measure.

Thanks in advance.

Just to confirm, is it possible to create a summarize kind of table in order to use 

[Ashish_Mathur]

Thank you.  I am not clear about your question.  Share some data and show the expected result.

[sivarajan21]

Hi Ashish,

Apologise for not making it clear!

Below is my example dataset 

	Data(Date)
Points Id	Data Date	Source
123	04/01/2022	Invoice
123	04/02/2022	Profile
123	04/07/2022	Invoice
123	04/08/2022	Profile
123	04/09/2022	Profile
123	04/10/2022	Profile
123	04/11/2022	Profile

Calendar date

Date

01/04/2022

02/04/2022

03/04/2022

04/04/2022

05/04/2022

06/04/2022

The above 2 tables are to be compared. What we need to find is the missing calendar dates of points id(data table). That is, the points id '123' in Data table has missing dates 03rd april 2022 that is present in calendar table. so we need to return that missing date in a table format/summarised table. 

Expected outcome

Points Id	Missing Calendar date
123	03/04/2022
123	04/04/2022
123	05/04/2022
123	06/04/2022

Please let me know if you need further info

Thanks in advance 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[sivarajan21]

Hi Ash,

Brilliant, exactly the solution i was looking for and many thanks.

If there was a rating here in community, I would give 5 star(maximum) for your answer.

I will close this long query and accept your answer as a solution.

Thank you and the community for the help

[Ashish_Mathur]

You are welcome.  Thank you for your kind words.

[sivarajan21]

Hi,

Still looking for a solution and highly appreciated if someone helps

Thanks

[grantsamborn]

Hi @sivarajan21 

It appears that your sample data, measure and expected results don't match.

Check that both 'Calendar'[Date] and 'Data'[Date] have a datatype of date (not datetime).

This example would show all dates in 'Calendar'[Date] that are missing a match with 'Data'[Date]  --- including all dates in the date table that are before the first 'Data'[Date] and dates after the last 'Data'[Date].

Missing Dates Table 1 = 
FILTER(
    'Calendar',
    NOT ( 'Calendar'[Date] IN VALUES( Data[Date] )
    )
)

If you don't want to see those additional dates, maybe try this.

Missing Dates Table 2 = 
VAR _StartDt = MINX( ALL( Data[Date] ), Data[Date] ) - 1
VAR _EndDt = MAXX( ALL( Data[Date] ), Data[Date] ) + 1
VAR _Result =
    FILTER(
        'Calendar',
        NOT ( 'Calendar'[Date] IN VALUES( Data[Date] ) )
            && 'Calendar'[Date] >= _StartDt
            && 'Calendar'[Date] <= _EndDt
    )
RETURN
    _Result

You might need to adjust the _StartDt and _EndDt variables as shown or simply just use the MINX and MAXX values. 

In your example without adjusting the variables, the result would be dates from 4/3/2022 to /4/6/2022.

With adjustments made to variab les, the result would be 3/31/2022, 4/3/2022, 4/4/2022, 4/5/2022, 4/6/2022, and 4/12/2022.  

Note that with the adjustments made, there could be a problem for the 1st day or last day of your calendar.

I hope this makes sense.

[grantsamborn]

Hi @sivarajan21 

I thought you needed a calculated table instead of a matrix or table visual.  My mistake.

I'l be busy for the next 4 hours but will take a look at it later.

Grant

[grantsamborn]

Hi @sivarajan21 

I'm sorry I didn't get back to you.  I'm drawing a blank here.

Maybe you should ask a few of the Super-Users.

[sivarajan21]

Hi @grantsamborn 

No problem and thanks for your prompt response😊

Don't know how to contact super users. Any lead could be appreciated

Thanks in advance

[grantsamborn]

Maybe one of these can help?

@amitchandak  @lbendlin  @Greg_Deckler  @Ashish_Mathur 

[lbendlin]

A measure can contain table variables but ultimately MUST return a scalar value.  You cannot "distribute measure results across rows", instead you need to have a disconnected table with row numbers and then you can calculate the measure output separately for each row.

[sivarajan21]

Hi,

@grantsamborn 

Brilliant!

This(Missing Dates table 1) is what i wanted, finding(picking) Calendar dates that is not present(missing) in the Data date column.

I tried this measure in my pbix file and it shows the below error.

I would like to show all the missing dates in the matrix visual(listed one below other) and filtered by points id. The screenshot below shows expected outcome but dates must be listed one below other(list-matrix visual).

Don't want to omit any dates.

Your measure helps me to achieve my below description but I wanted to visualize those missing dates ina matrix visual and it should be filtered by points id.

Description of my original problem:

I have 2 tables calendar table, and Data table. I have provided the sample data of these tables below:

Below is my sample Data table

DATA  

Date	Points	Source	Units	Last_Update	Cost
3/25/2021 12:00:00 AM	NorthYorksCC-2076	Direct	84.39	2/22/2023	0
3/25/2021 12:00:00 AM	NorthYorksCC-2076	Direct	84.39	2/22/2023	0
3/27/2021 12:00:00 AM	NorthYorksCC-2076	Direct	84.39	2/22/2023	0
6/28/2022 12:00:00 AM	INSE-1010	Invoice	0.0806	2/22/2023	0.16
6/30/2022 12:00:00 AM	INSE-1010	Invoice	0.0806	2/22/2023	0.16

Calendar table

Date

25-Mar-21

26-Mar-21

27-Mar-21

28-Mar-21

28-Jun-22

29-Jun-22

30-Jun-22

we need to find the missing date for each point id from data table by comparing it with calendar table date. for example, from the above sample data, if we look at the data table, 

the points (NorthYorksCC-2076) is missing the date 3/26/2021 12:00:00, but when we compare it with Calendar Date, it has 26-Mar-21. similarly, INSE-1010 is missing date 6/29/2022 12:00:00 AM  but calendar date, has 29-Jun-22.   I need to display these missing dates & their count in output. 

My expected outcome could be something like below:

points id	missing dates measure	no of missing dates measure
NorthYorksCC-2076	26/03/2021	1
INSE-1010	29/06/2022	1

we need one dax measure to display the above missing dates as a list to be shown on the report page.

we need another dax measure to display the count of those above missing dates.

The relationship between above tables are 

Please let me know if you need further info

Thanks in advance

[Arul]

@sivarajan21 ,

this is what I have achieved so far, just able to find the missing dates in a new calculated table,

Max date1 = 
VAR _distinctDates = VALUES('Table'[Date])
VAR _maxDate = CALCULATE(
    MAX('Table '[Date]),
    ALLEXCEPT('Table ','Table '[Points]))
VAR _minDate = CALCULATE(
    MIN('Table '[Date]),
    ALLEXCEPT('Table ','Table '[Points]))
VAR _calendar = CALENDAR(
    _minDate,_maxDate)
VAR _compare = EXCEPT(_calendar,_distinctDates)
RETURN _compare

Thanks,

Arul

[sivarajan21]

Hi Arul,

Thanks for your response

However for some reason the table generated has missing year 2022.

Also, I couldn't filter this table using points id even if i establish a relationship between these two.

Thanks in advance.

[onurbmiguel_]

Hi @sivarajan21 

Do you have a calendar table? 

Why do not use the date in the Rows? 

If you need more help please share a pbix with some dummy values.

Best regards

Bruno Costa | Impactful Individual

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍

You can also check out BI4ALL's website and our data solutions!

[sivarajan21]

Hi,

@onurbmiguel_ 

Thanks for your response

Here I wanted to find the missing dates between 2 columns and I have given a detailed description above.

Yes I will provide a pbix file but before that please read my description given above. Hope that will give you more information.

please let me know if you need further info

Thanks in advance.