VAR doesn't work

[larabraghetti]

Dear all,

I tried to use VAR in PowerBI but without success.

Here below my measure:

MaxTest =
VAR LVL1 =
    COUNTAX ( LIMLOGN; FILTER ( ALL ( LIMLOGN[LvlCode] ); LIMLOGN[LvlCode] = 1 ) )
VAR LVL2 =
    COUNTAX ( LIMLOGN; FILTER ( ALL ( LIMLOGN[LvlCode] ); LIMLOGN[LvlCode] = 2 ) )
VAR LVL3 =
    COUNTAX ( LIMLOGN; FILTER ( ALL ( LIMLOGN[LvlCode] ); LIMLOGN[LvlCode] = 3 ) )
VAR LVLTOT =
    COUNTAX ( LIMLOGN; ALL ( LIMLOGN[LvlCode] ) )
RETURN
    IF (
        IF ( LVL1 >= LVL2; IF ( LVL1 >= LVL3; LVL1; IF ( LVL2 >= LVL3; LVL2; LVL3 ) ) )
            < LVLTOT;
        "Y";
        "N"
    )

When I try to put this measure into a Pivot table I will obtain the following error:

Mdx Script (Model) (9, 24) Calculation error in the measure 'LIM LOGN' [MAXTEST]: You specified a table of values as expected while a single value.

I have built 4 Variables and I'd like to find the maximum between LVL1,LVL2,LVL3 

Then I need to compare the Max with LVLTOT and return "Yes" or "Not".

Could you help me to understand where is my error?
Thank you
Lara

[Anonymous · Accepted solution]

@larabraghetti

The expressions will have to be written as follows

VAR LVL1 =
    COUNTAX ( FILTER ( LIMLOGN, LIMLOGN[LvlCode] = 1 ) ,LIMLOGN[LvlCode] )
VAR LVL2 =
    COUNTAX (  FILTER ( LIMLOGN,LIMLOGN[LvlCode] = 2 ), LIMLOGN[LvlCode] )
VAR LVL3 =
    COUNTAX ( FILTER ( LIMLOGN, LIMLOGN[LvlCode] = 3 ) ,LIMLOGN[LvlCode] )
VAR LVLTOT =
    COUNTAX ( ALL ( LIMLOGN ) ,LIMLOGN[LvlCode] ) 
RETURN
    IF (
        IF ( LVL1 >= LVL2; IF ( LVL1 >= LVL3; LVL1; IF ( LVL2 >= LVL3; LVL2; LVL3 ) ) )
            < LVLTOT;
        "Y";
        "N"
    )

It should work.  

If this works please accept it as a solution and also give KUDOS

Cheers

CheenuSing

[Sundeep]

I am trying to return all selected items from a slicer, PowerBI says "the syntax for ')' is incorrect."

All Selected Items =

VAR Selected_Item_Names = ALLSELECTED(AccountName_ItemName[Item Name])

return

[KHorseman]

Your syntax is wrong in all of those COUNTAX formulas. The syntax for COUNTAX is =COUNTAX(<table>, <expression>) but you seem to have written it as =COUNTAX(<table>, <table>). The FILTER statement should be your first argument and the second should be an expression to be evaluated on each row. So VAR LVL1 = COUNTAX( FILTER( ALL (LIMLOGN), LIMLOGN[LvlCode] = 1), <insert whatever expression you're trying to evaluate here>)

But if I understand the results you're trying to get, I don't think you should even be using COUNTAX in the first place. If you just want to count the rows in a filtered table you should use COUNTROWS instead of COUNTAX. COUNTAX should be used if you want to evaluate some expression on each row of a table and count the results of that expression. So if you just want a count of all rows where LIMLOGN = 1, it should be

VAR LVL1 = COUNTROWS(FILTER( ALL (LIMLOGN), LIMLOGN[LvlCode] = 1))

[KGrice]

Hi @larabraghetti. I don't see anything explicitly defined for when LVL1 < LVL2, as in:

IF ( LVL1 >= LVL2; IF ( LVL1 >= LVL3; LVL1; IF ( LVL2 >= LVL3; LVL2; LVL3 ) ); LVL2 )

I'm also not sure how this would ever evaluate to anything but "N". Isn't a part of the whole always less than the whole? If you get the count of a column where the code is only 1, and then a count of the column where the code doesn't matter at all, wouldn't the second calculation always be greater than the first? Sorry if I'm missing something.

[larabraghetti]

The error is in the variable LVLTOT:
VAR LVLTOT =
COUNTAX ( LIMLOGN; ALL ( LIMLOGN[LvlCode] ) )

But I don't understand the reason... Any suggestion?

Thank you
Lara