DAX: How to ask for different output depending on a count of strings

[love]

Hello.

I have a data set that looks like this:

Config	ID	Output
A	1	A1
A	2	A2
B	3	B3
C	4	C4

My goal is it to change the "Output" column (which is a calculated column) to display this:

Config	ID	Output
A	1	various
A	2	various
B	3	B3
C	4	C4

The logic behind this here is: If the same Config has different IDs then Output should return "various" instead of the individual Config+ID combination. My initial idea was to count the amount of different IDs per config and if that count is bigger than 1 then the calculated "Output" column should return the string "various".

I tried some combinations with CALCULATE, COUNT, COUNTA, COUNTAX withint IF conditions but was not able to get to my goal.

Eventually the goals is to hide the ID column and to then display this:

Config	Output
A	various
B	B3
C	C4

Thank you for your support,

-L

[djdonnel · Accepted solution]

Hi @love, 

Here are a couple of options.

If your data are set up so that Config + ID combination is always unique you could use this:

Output =
IF (
    COUNTROWS ( CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[Config] ) ) ) > 1,
    "Various",
    CONCATENATE ( Table[Config], Table[ID] )
)

But if you have A1 appear twice and you don't want the result to be "various" you could use this alternative:

Output_v2 =
IF (
    CALCULATE (
        COUNTROWS ( CROSSJOIN ( VALUES ( Table[Config] ), VALUES ( Table[ID] ) ) ),
        ALLEXCEPT ( Table, Table[Config] )
    )
        > 1,
    "Various",
    CONCATENATE ( [Config], [ID] )
)

[love]

Hi @djdonnel, thank you very much for your reply and the suggested solutions.

Both work fine on the example data I provided.

I am running into an issue though when running this formula over my actual data. What I mistakenly did not include in my initial requirements is that there are a bunch of additional columns, which (so I think) increase the value returned by the CALCULATE function (from your second proposed solution) to a value that is variable based on how many unique combinations (CROSSJOIN) exist. The consequence is that the proposed solution pretty much always returns "various" for me and there is no single value I can change the ">1" to in order to fix this.

Would there be a way to tell the calculation to ignore all columns or ID/config combination caused by columns which are not ID/Config (provided my assumption on this being the cause for my issue is correct)?

I am not a coder and not an expert on PowerBI but I'd be glad to compile a set of sample data in which the problem is visible if that would help.

Thanks again for your help.