Counter with increment based on contents of multiple columns

[MoeData]

I woud like to make a table or matrix visual where I can have a column as a counter that increases by 1 when either of the values in other 2 columns changes for each row. 

As shown in the attached picture, I have two columns of Class and Name from the same table. I want to show those columns along with a measure and a counter. The counter should start at 1 and increase by 1 (and only 1) when either of Class or Name (or both) changes as we go down the rows. There are going to be filters applied to Class and Name as well and the number the counter shows should always start from 1 and increase by 1. By the way the visual will have several other columns as well that I have not shown in the screenshot, so rows are not going to be identical.

The very end goal is to apply custom color formatting on the rows when the counter changes, as shown in this article:
https://www.sqlbi.com/articles/applying-color-banding-by-document-number-in-power-bi/

I also looked into another article, where someone in the comments suggests using SUBSTITUTEWITHINDEX, but I could not make it work:
https://www.sqlbi.com/articles/rankx-on-multiple-columns-with-dax-and-power-bi/

I would appreciate to see some DAX codes that can build this counter.
Thanks!

[MoeData]

@OwenAuger 
Thanks, it works well. 
Do you know any ways to make this DAX  code more general, meaning that it always works with the columns in the matrix (or table) visual, regardless of the quantities shown on the columns? 
If I have 50 different matrix (or table) visuals with different columns then I dont want to make 50 different measures that are pretty much identical and doing the same job.  I mean if for example I have "country" instead of "class" in the visual, is there a way that I can use the very same DAX code, without changing "class" to "country" in the DAX?

Best,
Moe

[OwenAuger]

Hi Moe,

Glad that it works 🙂

I'm not aware of any way of writing a general DAX expression for this. Unfortunately there's no generic index function. Tableau's INDEX() function springs to mind as an example in another tool.

The best I can suggest right now is using Tabular Editor with some sort of script to make the creation of these measures more efficient.

Here's hoping that visual calculations in DAX come to pass, some time soon!

All the best,

Owen

[MoeData]

Hi @OwenAuger 

Thanks for the link! Very useful! 

Regarding using Tabular Editor, I found this video a while ago and it helped me to reduce the number of measures I create in another report. Since in the current report, I am using column name and not a measure in the "counter", I am not sure how the same concept can be used. Do you maybe have some links where Tabular Editor can be useful in this current scenario and making different "counter"s in an efficient way?

Cheers,
Moe 

 

[OwenAuger]

Hi again @MoeData ,

You're welcome 🙂

What I was thinking was a C# script where it creates a "sorting measure" based on the selected columns.

You would select the relevant columns, run the script, and have it generate the measure for you.

Then select a different set of columns and repeat.

Similar to what's shown to the first example here, but rather than creating a measure for each column selected, you would want to create one measure referencing all the columns selected.

https://docs.tabulareditor.com/te2/Useful-script-snippets.html

You could also possibly provide an input file listing all the variations of column combinations, then use that as input to the script.

Another thought: once you have created the various sorting measures, you could write a master measure that selects the relevant sorting measure based on columns that are present in the table visual. The "presence" of columns (at least in a table) can be detected using the ISINSCOPE function. Then the same measure could be used on various different visuals.

Regards,

Owen

[OwenAuger · Accepted solution]

Hi @MoeData 

The measure using SUBSTITUTEWITHINDEX method from the SQLBI article comments is quite smart 🙂

Here it is, adapted to your requirements (PBIX attached as well):

Counter = 
VAR CurrentClass =
    SELECTEDVALUE ( 'Table'[Class] )
VAR CurrentName =
    SELECTEDVALUE ( 'Table'[Name] )
RETURN
    IF (
        NOT ISBLANK ( CurrentClass )
            && NOT ISBLANK ( CurrentName ),
        VAR RightJoinTable =
            -- You may need to modify this expression to include only rows where
            -- at least one measure displayed in visual is nonblank
            CALCULATETABLE (
                SUMMARIZE ( 'Table', 'Table'[Class], 'Table'[Name] ),
                ALLSELECTED ()
            )
        VAR LeftJoinTable =
            ADDCOLUMNS ( RightJoinTable, "@Class", 'Table'[Class], "@Name", 'Table'[Name] )
        VAR IndexedTable =
            SUBSTITUTEWITHINDEX (
                LeftJoinTable,
                "MyRank", RightJoinTable,
                'Table'[Class], ASC,
                'Table'[Name], ASC
            )
        VAR FilteredTable =
            FILTER ( IndexedTable, [@Name] = CurrentName && [@Class] = CurrentClass )
        VAR Result =
            MAXX ( FilteredTable, [MyRank] ) + 1
        RETURN
            Result
    )

With the code above, the natural sort order of the Class and Name columns will be followed, which is presumably what you want if you want to match the visual's default sort order.

You could change the "order" arguments of SUBSTITUTEWITHINDEX (arguments 4-7) if you want to customize the sort order in any way.

Also, as noted in the code, you may need to modify the expression for RightJoinTable to include only rows where at least one measure displayed in the visual is nonblank.

Regards,

Owen