CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Johnny Spindler
Johnny Spindler

Reputation: 456

Power BI DAX - MAX of COUNT strings vs numbers

My current mesure for "Orders" elects the MAX of the MAX of two number values.
enter image description here 

Range MAX Orders = MAXX(
    {
        Max(Ziele[ScopeOrd]),
        MAX(Ist[Orders])
    },
    [Value]
)

The Result is correct = 35

My problem is, that for my "Company" mesure I have the string Names of the companies and the numbers for my scope.
enter image description here

So I guess that I need to create a count mesure first, but it should still be in the context of the countries. Or is it possible to put all in one?
I have tried:

Range MAX Orders = MAXX(
    {
        Max(Ziele[ScopeComp]),
        MAX(COUNT(Ist[Company]))
    },
    [Value]
)

Unfortunatly that results in an error: 
The MAX function only accepts a column reference as an argument.



Please let me know how to solve it best.

Here is some example data.
Table Ist:
Company|ISO2
Acme Corporation|US
Globex Corporation|US
Soylent Corp|DE
Initech|DE
Umbrella Corporation|IT
Hooli|US
Vehement Capital Partners|US

Table Ziele:
ScopeComp|ISO2
2|US
3|DE
2|IT


The Result for the exampe data should be = 4
Since there is a COUNT of 4 companies in the US and this is greater then the number 3 MAX of the ScopeComp.

Upvotes: 0

Views: 401

Answers (1)

Ozan Sen
Ozan Sen

Reputation: 2615

Try this one:

Range MAX Orders = 
VAR Tbl = MAXX(ADDCOLUMNS(
            VALUES(Ist[ISO2]),
            "TCount",CALCULATE(COUNT(Ist[ISO2]))
),[TCount])
VAR Tbl2 = MAX(Ziele[ScopeComp])
RETURN
    MAXX(
        {Tbl,Tbl2},[Value])

If we test it on a visual card,

Final

Upvotes: 1

Related Questions