Forum Discussion

sivarajan21's avatar
sivarajan21
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

Convert Comma separated dax measure into a list

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 pe...
Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

sivarajan21's avatar
sivarajan21
Icon for Post Prodigy rankPost 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  

DatePointsSourceUnits Last_UpdateCost
3/25/2021 12:00:00 AMNorthYorksCC-2076Direct84.392/22/20230
3/25/2021 12:00:00 AMNorthYorksCC-2076Direct84.392/22/20230
3/27/2021 12:00:00 AMNorthYorksCC-2076Direct84.392/22/20230
6/28/2022 12:00:00 AMINSE-1010Invoice0.08062/22/20230.16
6/30/2022 12:00:00 AMINSE-1010Invoice0.08062/22/20230.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 idmissing dates measureno of missing dates measure
NorthYorksCC-207626/03/20211
INSE-101029/06/20221

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's avatar
Arul
Icon for Super User rankSuper 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

  • sivarajan21's avatar
    sivarajan21
    Icon for Post Prodigy rankPost Prodigy
    3 years ago

    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.