Return column of other table for each date in date table

[Anonymous]

Hi all,

I am currently stuck in my mind, hope you can help me out.

I have two tables in my datamodel. One fact table named 'Dossier', translated to English it is file. In the Dossier table I have a column 'Datum in werkvoorraad' and 'Datum uit werkvoorraad'. You can translate werkvoorraad to pipeline I think. It says something about when there was being worked on a file, a from to and until date.

I also have a seperate date table, which contains a full one year back. So in DAX terms: 

calendar(today() - 365, today())

I want to add a DAX table which contains the following fields:

Date

Dossier ID (unique)

The result will look something like this:

03-01-2022 234234123

03-01-2022 234341232

03-01-2022 142232

04-01-2022 234234123

04-01-2022 142232

What I have accomplished is the following: 

I have created a table which can return the list for one date. Using a variable called 'meetdatum', which should be the date from the date table. I will check for each date which dossiers (files) are currently in the werkvoorraad (pipeline):

var meetdatum = date(2022, 1, 3)

var temptabel = filter(dossier, Dossier[Status - Datum aanvraag] <= meetdatum &&

((COALESCE(Dossier[Status - Datum finaal akkoord], Dossier[Status - Beëindigd op]) > meetdatum) ||

(isblank(COALESCE(Dossier[Status - Datum finaal akkoord], Dossier[Status - Beëindigd op]))) &&

(Dossier[D_RedenBeeindiging] <> 5 || isblank(Dossier[D_RedenBeeindiging])) &&

Dossier[Ind niet tellen (dubbel dossier)] = "Meetellen" ))

return SELECTCOLUMNS(temptabel, "ID dossier", Dossier[id Dossier], "Datum", meetdatum )

How can I get this to work for a full year? So all dates and dossier ID's are unioned?

[johnt75]

You can use GENERATE, .e.g 

New Table = GENERATE( ALLNOBLANKROW('Date'[Date]),
var meetdatum = SELECTEDVALUE( 'Date'[Date])
 
var temptabel = filter(dossier, Dossier[Status - Datum aanvraag] <= meetdatum &&
((COALESCE(Dossier[Status - Datum finaal akkoord], Dossier[Status - Beëindigd op]) > meetdatum) ||
(isblank(COALESCE(Dossier[Status - Datum finaal akkoord], Dossier[Status - Beëindigd op]))) &&
(Dossier[D_RedenBeeindiging] <> 5 || isblank(Dossier[D_RedenBeeindiging])) &&
Dossier[Ind niet tellen (dubbel dossier)] = "Meetellen" ))
)

[Anonymous]

Hi John, thank you for your quick reply.

I have adjusted the DAX formula in the table but I am getting an error:

[johnt75]

I think the error is in the code you originally posted, when I tried to use DAX formatter it gave an error.

Take your existing code and use that as the 2nd argument to GENERATE, replacing the definition of meetdatum with the version I posted.

[Anonymous]

Hi John,

First of all, thanks again 🙂

I have replaced the code with the existing code, but I am getting no result in the table:

[johnt75]

Hmm, it all looks correct. Is it possible to share the PBIX if it doesn't contain confidential information?

Another thing you could try is replacing the SELECTEDVALUE for the meetdatum with an actual fixed date which you know works, as in your original post. That would at least prove that the code is working correctly.

[Anonymous]

I just tried, the weird thing is that all dates seem to be returned:

I would expect that only the filled in date would be in the list (15th of januari 2021).

Sadly I am not able to share the .pbix, there is confidential information in the file.

[johnt75]

That behaviour is expected. Its returning all the dates because at the moment there is no connection between the date column and the calculation, its just using one flat date so it will return the same rows for every entry in the date column.

The only small thing I can see is that your date column is a datetime not just a date. I don't know if that will make any difference, but you could try changing it to be a date type. Other than that I think I'm out of ideas.

[Anonymous]

I changed both the date in the werkvoorraad 2 table and time table to just date, not date/time. It did not resolve the issue. My current DAX formula for the werkvoorraad table is now:

Werkvoorraad 2 = 
GENERATE( ALLNOBLANKROW(Werkvoorraad[Datum]),
var meetdatum = SELECTEDVALUE( Werkvoorraad[Datum])
 
var temptabel = /*filter(Dossier, Dossier[Status - Datum aanvraag] <= meetdatum &&
((COALESCE(Dossier[Status - Datum finaal akkoord], Dossier[Status - Beëindigd op]) > meetdatum) ||
(isblank(COALESCE(Dossier[Status - Datum finaal akkoord], Dossier[Status - Beëindigd op])))) &&
(Dossier[D_RedenBeeindiging] <> 5 || isblank(Dossier[D_RedenBeeindiging])) &&
Dossier[Ind niet tellen (dubbel dossier)] = "Meetellen" )*/

filter(Dossier, Dossier[Status - Datum aanvraag] <= meetdatum &&
(COALESCE(Dossier[Status - Datum finaal akkoord], Dossier[Status - Beëindigd op]) > meetdatum ||
isblank(COALESCE(Dossier[Status - Datum finaal akkoord], Dossier[Status - Beëindigd op]))) &&
(Dossier[D_RedenBeeindiging] <> 5 || isblank(Dossier[D_RedenBeeindiging])) &&
Dossier[Ind niet tellen (dubbel dossier)] = "Meetellen" )

return SELECTCOLUMNS(temptabel, "ID dossier", Dossier[id Dossier])

)

[johnt75]

Just as a test, try the below

Test Table = GENERATE(ALLNOBLANKROW(Werkvoorraad[Datum]),
ROW( "same date", SELECTEDVALUE(Werkvoorraad[Datum])
)

just to make sure that SELECTEDVALUE is working as I think it and is correctly picking up the row context. It should give you a 2 column table with the same date in each column.

[Anonymous]

Hi John,

This is the result:

[johnt75]

Try

Test Table = GENERATE(ALLNOBLANKROW(Werkvoorraad[Datum]),
CALCULATETABLE( ROW( "same date", SELECTEDVALUE(Werkvoorraad[Datum]))
)

that will force context transition and we may need a CALCULATETABLE in the main table if this works

[Anonymous]

Hi John, thanks again!

It works in the test table now:

How do I integrate the calculatetable in the existing table? I tried but I don't know exactly what I am doing, and it also does not work. This is my currect query:

Werkvoorraad 2 = 
GENERATE( ALLNOBLANKROW(Werkvoorraad[Datum]),
var meetdatum = SELECTEDVALUE( Werkvoorraad[Datum])
 
var temptabel = 

calculatetable(filter(Dossier, Dossier[Datum in werkvoorraad] <= meetdatum &&
(Dossier[Datum uit werkvoorraad] > meetdatum || isblank(Dossier[Datum uit werkvoorraad]))))

return SELECTCOLUMNS(temptabel, "ID dossier test", Dossier[id Dossier])

)

[Anonymous]

@johnt75 I am not getting it to work. My current code:

Werkvoorraad 2 = 
GENERATE( ALLNOBLANKROW(Kalender[Datum]),
var meetdatum = SELECTEDVALUE( Kalender[Datum])
 
var temptabel = 
filter(Dossier, Dossier[Datum in werkvoorraad] <= meetdatum &&
(Dossier[Datum uit werkvoorraad] > meetdatum || isblank(Dossier[Datum uit werkvoorraad]))) 

/*return calculatetable(row("ID dossier", SELECTCOLUMNS(temptabel, "ID dossier test", Dossier[id Dossier])))*/

return calculatetable(selectcolumns(temptabel, "ID dossier", Dossier[id Dossier]))

)

[johnt75 · Accepted solution]

try

Werkvoorraad 2 = 
GENERATE( ALLNOBLANKROW(Kalender[Datum]),
CALCULATETABLE(
var meetdatum = SELECTEDVALUE( Kalender[Datum])
 
var temptabel = 
filter(Dossier, Dossier[Datum in werkvoorraad] <= meetdatum &&
(Dossier[Datum uit werkvoorraad] > meetdatum || isblank(Dossier[Datum uit werkvoorraad]))) 


return selectcolumns(temptabel, "ID dossier", Dossier[id Dossier])
)
)

[Anonymous]

Wow, this worked! Thanks John!