Error in segmentation data

[jlarques]

Hi everyone,

I defined a data segmentation rank to be able to apply some measures in my graphics, but if I don't choose any rank option, the control show me an error. 

The measure formula is this:

Sales by Rank =
IF(

    ISBLANK(VALUES(RANK[RANK]));   

         BLANK();

    SUMX(
         FILTER(
              VALUES( CLIENTES[CLI_NOM]);
              [TotalSales]>=VALUES(RANK[FROM]) && [TotalSales]<=VALUES(RANK[TO) );
[TotalSales] ) )

[jlarques · Accepted solution]

Hi everyone,

finally, I have the right formula to be able let the rank in blank if the user doesn't choose any option. The key was the command SELECTEDVALUE instead of VALUES. Here is the right formula:

Rank x Customers =
IF(SELECTEDVALUE(

      RANK[RANK])=BLANK();

      SUMX(
          FILTER(
              VALUES( CUSTOMERS[CUSTOMERNAME]);
                    [TotalSales]>=0 && [TotalSales]<=1000) ;
                    [TotalSales] );

    SUMX(
        FILTER(
            VALUES( CUSTOMERSCUSTOMERNAME]);
                 [TotalSales]>=VALUES(RANK[FROM]) && [TotalSales]<=VALUES(RANK[TO]) );
                 [TotalSales] ) )

If the user doesn't choose anything and the RANK[RANK] is blank, using SELECTEDVALUES I assign the first option(Until 1.000€) applying TotalSales between 0 and 1000.

Thanks everybody for its contributions to this community.

[v-shex-msft]

Hi @jlarques,

Values function will return a list from selected value, it will return error when you try to use math symbol to compare with list of value.(at row level, values return single value, but on total/summary level, it contains multiple values)

I add firstnonblank and lastnonblank to extract single value from list, maybe you can try it if it suitable for your requirement:

Sales by Rank =
IF (
    ISBLANK ( VALUES ( RANK[RANK] ) );
    BLANK ();
    SUMX (
        FILTER (
            VALUES ( CLIENTES[CLI_NOM] );
            [TotalSales] >= FIRSTNONBLANK ( VALUES ( RANK[FROM] ); [FROM] )
                && [TotalSales] <= LASTNONBLANK ( VALUES ( RANK[TO] ); [TO] )
        );
        [TotalSales]
    )
)

If above not help, please share some sample data to test.

Regards,

Xiaoxin Sheng

[jlarques]

Hi Xiaoxin,

thanks for your comments but the formula is not working. In the Sales by Rank I only have a Measure TotalSales(TotalSales=SUM(DATOS[IMPORTE]) and the customer name. When I choose one of the Rank options in the Rank amount, it works, as you can see in the images attached. The problem is when I don't choose anything in the Rank options.

Maybe would be a good option to have always the first option choosed. I don't know if this is possible or not. 

Thanks again for your help.

Jose Luis