Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
larabraghetti
Helper II
Helper II

VAR doesn't work

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

1 ACCEPTED SOLUTION
CheenuSing
Community Champion
Community Champion

@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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
CheenuSing
Community Champion
Community Champion

@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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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
Community Champion
Community Champion

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))





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




KGrice
Memorable Member
Memorable Member

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
Helper II
Helper II

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.