Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

SelectedValue Measure DAX not working

Hello all,

 

I am currently stuck in getting this corrected and doesn't seem to work somehow. I have a slicer selection on the page - for STORE. I am trying to set condition based on different STORE selection from slicer. Below is the DAX measure I have created:

 

BrandName SelectedFormula =
var SelectedValue = SELECTEDVALUE(store[cdk_accounting_account])
RETURN
SWITCH(TRUE(),
SelectedValue = "PNH-A", (IF(inventoryvehicle_v[Brand Name] = "Porsche", inventoryvehicle_v[Brand Name], "Others")),
SelectedValue = "LH-A", (IF(inventoryvehicle_v[Brand Name] = "LAMB" || inventoryvehicle_v[Brand Name] = "LAM" || inventoryvehicle_v[Brand Name] = "MCLA" || inventoryvehicle_v[Brand Name] = "MCLRN", inventoryvehicle_v[Brand Name], "Others")))
 
The issue that I am running into is: If you look at both the IF statements, these are not aggregates which it is needed to run this logic. For us, it cannot be an aggregates since it is referring a scalar value comparison.
 
Below is the screenshot error I get:
 
 
Can anyone suggest me a workaround to write this? Any help is appreciated!
 
Regards!
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    According to your description, I create a sample.

    The two tables have relationship.

    Columns cannot be directly quoted in measure, as measure is aggregation operation, it can only refer to unique values, not a column. You can add MAX function to return the value of the current row, like this:

    BrandName SelectedFormula = 
var SelectedValue = SELECTEDVALUE(store[cdk_accounting_account])
RETURN
SWITCH(TRUE(),
SelectedValue = "PNH-A", IF(MAX(inventoryvehicle_v[Brand Name]) = "Porsche", MAX(inventoryvehicle_v[Brand Name]), "Others"),
SelectedValue = "LH-A", IF(MAX(inventoryvehicle_v[Brand Name]) IN{ "LAMB" ,"LAM" ,"MCLA" ,"MCLRN"}, MAX(inventoryvehicle_v[Brand Name]), "Others"))

    In this way, the measure works, but the brand name cann't be filtered by slicer.

    You can modify the formula like this:

    BrandName SelectedFormula 2 =
VAR SelectedValue =
    SELECTEDVALUE ( store[cdk_accounting_account] )
RETURN
    SWITCH (
        TRUE (),
        SelectedValue = "PNH-A",
            IF (
                MAX ( 'inventoryvehicle_v'[account] ) = "PNH-A",
                IF (
                    MAX ( inventoryvehicle_v[Brand Name] ) = "Porsche",
                    MAX ( inventoryvehicle_v[Brand Name] ),
                    "Others"
                ),
                BLANK ()
            ),
        SelectedValue = "LH-A",
            IF (
                MAX ( 'inventoryvehicle_v'[account] ) = "LH-A",
                IF (
                    MAX ( inventoryvehicle_v[Brand Name] ) IN { "LAMB", "LAM", "MCLA", "MCLRN" },
                    MAX ( inventoryvehicle_v[Brand Name] ),
                    "Others"
                ),
                BLANK ()
            )
    )

    Here's the result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    According to your description, I create a sample.

    The two tables have relationship.

    Columns cannot be directly quoted in measure, as measure is aggregation operation, it can only refer to unique values, not a column. You can add MAX function to return the value of the current row, like this:

    BrandName SelectedFormula = 
var SelectedValue = SELECTEDVALUE(store[cdk_accounting_account])
RETURN
SWITCH(TRUE(),
SelectedValue = "PNH-A", IF(MAX(inventoryvehicle_v[Brand Name]) = "Porsche", MAX(inventoryvehicle_v[Brand Name]), "Others"),
SelectedValue = "LH-A", IF(MAX(inventoryvehicle_v[Brand Name]) IN{ "LAMB" ,"LAM" ,"MCLA" ,"MCLRN"}, MAX(inventoryvehicle_v[Brand Name]), "Others"))

    In this way, the measure works, but the brand name cann't be filtered by slicer.

    You can modify the formula like this:

    BrandName SelectedFormula 2 =
VAR SelectedValue =
    SELECTEDVALUE ( store[cdk_accounting_account] )
RETURN
    SWITCH (
        TRUE (),
        SelectedValue = "PNH-A",
            IF (
                MAX ( 'inventoryvehicle_v'[account] ) = "PNH-A",
                IF (
                    MAX ( inventoryvehicle_v[Brand Name] ) = "Porsche",
                    MAX ( inventoryvehicle_v[Brand Name] ),
                    "Others"
                ),
                BLANK ()
            ),
        SelectedValue = "LH-A",
            IF (
                MAX ( 'inventoryvehicle_v'[account] ) = "LH-A",
                IF (
                    MAX ( inventoryvehicle_v[Brand Name] ) IN { "LAMB", "LAM", "MCLA", "MCLRN" },
                    MAX ( inventoryvehicle_v[Brand Name] ),
                    "Others"
                ),
                BLANK ()
            )
    )

    Here's the result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you for inputs and the example file! This resolved my issue.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Are you trying to create a new column, That will not take the selected value.

     

    If you are trying a new measure, then you need to take some aggregation on brand_name

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Amit,

       

      Yes that's an issue, I am trying to create a new measure. I am not sure what aggregation will come up here since it's a straighforward condition based on text.

       

      Regards!

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        4 years ago

        Anonymous , You have use like example

         

        Max(InventoryVehicleName[Brand]) = "Other"

         

        and it will search for brand name in context.

         

        Or write formula inside expression part of Sumx, maxx etc