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:Date01/04/202202/04/202203/04/202204/04/202205/04/202206/04/202207/04/202208/04/202209/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 Date04/01/2022 00:0004/02/2022 00:0004/07/2022 00:0004/08/2022 00:0004/09/2022 00:0004/10/2022 00:0004/11/2022 00:00 Calendar date Date01/04/202202/04/202203/04/202204/04/202205/04/202206/04/2022 Please let me know if you need further infoThanks in advance

Hi, You may download my PBI file from here. Hope this helps.

Convert Comma separated dax measure into a list

19 Replies

onurbmiguel_
Power Participant
3 years ago
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
  Post Prodigy
  3 years ago
  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.

grantsamborn

Solution Sage

3 years ago

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
Solution Sage
3 years ago
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
  Solution Sage
  3 years ago
  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

Post Prodigy

3 years ago

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

Super User

3 years ago

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

Ashish_Mathur
Super User
3 years ago
Hi,

You can get multiple rows in the same cell. In the CONCATENATEX() function, replace the "," with UNICHAR(10)
- sivarajan21
  Post Prodigy
  3 years ago
  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
    Super User
    3 years ago
    Thank you. I am not clear about your question. Share some data and show the expected result.
sivarajan21
Post Prodigy
3 years ago
Hi,

Still looking for a solution and highly appreciated if someone helps

Thanks

Forum Discussion

Convert Comma separated dax measure into a list

19 Replies

Related content

Convert measure to column

Convert calculated column into measure

Convert column to measure

How to convert Table/list into comma separated string

Thousand-Separator-and-Decimal-Separator