cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
grggmrtn Regular Visitor
Regular Visitor

Is this DAX possible in M? Relative references

I created and merged two index columns in M, in order to create two "shifted" columns - hereby creating a source and destination column for use in Sankey.

 

//Add two indexes - to create the destination columns. The final calculation of the destination indsats and paragraph 
    #"Added indeks" = Table.AddIndexColumn(#"Sortering", "Indeks", 0, 1),
    #"Added indeks1" = Table.AddIndexColumn(#"Added indeks", "Indeks.1", 1, 1),
//Merge the queries to create the shifted paragraf and indsats columns
    #"Merged Queries" = Table.NestedJoin(#"Added indeks1",{"Indeks.1"},#"Tilføjet indeks",{"Indeks"},"Tilføjet indeks1",JoinKind.LeftOuter),
    #"Expanded Added indeks1" = Table.ExpandTableColumn(#"Merged Queries", "Added indeks1", {"Indsats", "Paragraf"}, {"Indsats D", "Paragraf D"}),

From here, I added the following DAX formula, to create a new "Indsats Destination" column - it bastically says if [Borger.CPR Kort] (which is my person ID) is the same in two rows, and the [Paragraf] is the same in the two rows, then [Indats D], and then if [Visitation **bleep**] date is Today () then return [Indsats], else "Stopped"

 

Indsats Destination = 
IF (
	AND(
		'Nexus - Indsatser'[Borger.CPR Kort]
			= LOOKUPVALUE (
				'Nexus - Indsatser'[Borger.CPR Kort];
				'Nexus - Indsatser'[Indeks]; 'Nexus - Indsatser'[Indeks] + 1);
		'Nexus - Indsatser'[Paragraf]
			= LOOKUPVALUE (
				'Nexus - Indsatser'[Paragraf];
				'Nexus - Indsatser'[Indeks]; 'Nexus - Indsatser'[Indeks] + 1
            ));
    'Nexus - Indsatser'[Indsats D];
    IF (
        'Nexus - Indsatser'[Visitation stop] = TODAY ();
        'Nexus - Indsatser'[Indsats];
        "Stoppet"
    )
)

I added a more-or-less similar DAX formula to add a "Destination Paragraf" column

 

This works as hoped, but it's kinda... messy. I end up with two Index columns that I don't need, as well as "Indsats D" and "Paragraf D" that I can't get rid of, since I have to use them in my DAX formula in order to create "Indsats Destination" and "Paragraf Destination".

 

Is there any way I can "move" the DAX formula to M? I know there must be a way, but every time I try I get lost in syntax errors and I'm spending WAY too much time on this Smiley Very Happy

 

All columns except for [Visitation stop] are text, while [Visitation stop] is a date column.

 

Anyone have any good ideas?

 

 

 

**edited because apparently the Danish word for "stop" is a naughty word in English Smiley Happy

1 REPLY 1
Community Support Team
Community Support Team

Re: Is this DAX possible in M? Relative references

Hi @grggmrtn ,

 

Please describe your requirement with dummy data and desired result.

How to Get Your Question Answered Quickly

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.