Clean unused variables from measures

[ricky7uio]

Well, I love the new feature that highlights the unused variables in measures.

I would like to create a script to at least increase the speed on which I can clean the unused variables in my model.

I was provided this code some time ago by @otykier. This presents me the list of all the measures and the variable name. Is there any way to only delete that variable from the measure (programmatically, of course)? I have several measures to clean and I am looking for the smart way to clean them. Thank you!!

using Dax.Analyzer.Ast;
 
var output="";
foreach(var measure in Model.AllMeasures)
{
    var messages=measure.GetCachedSemantics().Messages;
    if(messages.FirstOrDefault(m => m.Type == MessageType.Warning && m.Message.StartsWith("Variable") && m.Message.EndsWith("is declared but never used.")) is DocumentMessage message)
    {
        output+=measure.DaxObjectFullName +": "+message.Message +Environment.NewLine;
    }
}
 
Info(output);

[ricky7uio]

if anyone is interested, I created this simple solution, answering my own question. Hope it's useful also for someone. (tried in TE3).
You have to run it several times until you get 0 in the last message. This is because of nested variables. If they are unused, they will be marked as unused only when the previous one is deleted.

using Dax.Analyzer.Ast;
using System.IO;
using System.Text.RegularExpressions;

int messageCount = 0;
List < string > extractedStrings =
   new List < string > ();

foreach(var measure in Model.AllMeasures)

{

   var messages = measure.GetCachedSemantics().Messages;
   if (messages.FirstOrDefault(
         m => m.Type == MessageType.Warning &&
         m.Message.StartsWith("Variable") &&
         m.Message.EndsWith("is declared but never used.")) is DocumentMessage message) {
      var matches = Regex.Matches(message.Message, "'(.*?)'");
      foreach(Match match in matches) {
         string VariableName = match.Groups[1].Value;

         string MeasureName =
            measure.DaxObjectFullName.Replace("[", "").Replace("]", "");
         string daxCode = measure.Expression;

         string pattern = $ @ "VAR\s+{Regex.Escape(VariableName)}\s*=\s*.*?(\bVAR\b|\bRETURN\b)";
         daxCode = Regex.Replace(daxCode, pattern, "$1", RegexOptions.Singleline);

         measure.Expression = daxCode;

         messageCount++;
      }
   }
}

Info("Number of messages iterated: " + messageCount);

[otykier]

Great solution - thanks for sharing!

[ricky7uio]

Thank you @otykier! All credits are for you who shared the core part of the solution :) Now I am wondering if it would be possible to create a BPA rule to avoid users to keep introducing unused variables. Do you think that would be possible?

[otykier]

In theory, BPA should be able to access GetCachedSemantics() on a measure, but such a rule would only work with TE3, as TE2 does not have the .GetCachedSemantics() API, and so I guess it's a little pointless at the moment, since TE3 users would get the warnings in the Messages view.

[ricky7uio]

Understood! I was thinking of a rule to return an error on DevOps to avoid the introduction of such in the model. But since TE2 doesn't support it, I think then it's not possible. Thank you anyway @otykier!