Forum Discussion

showy's avatar
showy
Helper II
1 year ago
Solved

Dynamic Measure with Fieldparameters

Hello   I have some sample data here.   I have a D_Item with the article 12345, which has 4 values (Value 1 - Value 4)   In the fact table I have a few quantities for the article 12345.   The...
Need Help
Show and Tell
Tips and Tricks
  • ajaybabuinturi's avatar
    ajaybabuinturi
    1 year ago

    showy could you please use beow updated measure to get correct Grand Total.

    _Measure(Updated)2 = 
Var SelectedColumn = SELECTEDVALUE('PriceFields'[PriceColumn])

Var Result = 
SUMX(D_Item, 
    Var Quantity = CALCULATE(SUM(F_Sales[Quantity]))
    Var SelectedPrice = SWITCH (SelectedColumn,
                        "Price1", D_Item[Price1],
                        "Price2", D_Item[Price2],
                        "Price3", D_Item[Price3],
                        "Price4", D_Item[Price4],
                        BLANK())
    Var Result = Quantity * SelectedPrice //Final Result1
    Var Result2 = IF(ISBLANK(SelectedPrice), Quantity, Quantity * SelectedPrice) //Final Result2
    RETURN
    Result2 //Based on business needs chose any one result
    )
    
RETURN
Result

     

    Thanks,
    If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

     

ajaybabuinturi's avatar
ajaybabuinturi
Super User
1 year ago

Hi showy, could you please share some sample data of .pbix file

  • ajaybabuinturi's avatar
    ajaybabuinturi
    Super User
    1 year ago

    Thanks showy for providing sample data. 
    Wanted to let you know that the Paramter Table doesn't return the expected results.  Hence, 
    I created a other table called PriceFields to ensure that to get slected Price value. Also I tweaked earlier provided Measure to achive requirement.
    I am attching a .pbix file for reference.

    Table: 

    PriceFields = 
DATATABLE(
    "PriceColumn", STRING,
    "Index", INTEGER,
    {
        {"Price1", 0},
        {"Price2", 1},
        {"Price3", 2},
        {"Price4", 3}
    }
)

    _Measure(Updated):

    _Measure(Updated) = 
Var SelectedColumn = SELECTEDVALUE('PriceFields'[PriceColumn])
Var SelectedPrice = SWITCH(SelectedColumn,
                    "Price1", SELECTEDVALUE('D_Item'[Price1]),
                    "Price2", SELECTEDVALUE('D_Item'[Price2]),
                    "Price3", SELECTEDVALUE('D_Item'[Price3]),
                    "Price4", SELECTEDVALUE('D_Item'[Price4]),
                     BLANK()
                    )
Var Result = [SUM] * SelectedPrice //Final Result1
Var Result2 = IF(Result = BLANK(), [SUM], Result) //Final Result2
RETURN
Result2 //Based on business needs chose any one result

    Expected Result:

    Here is the .pbix file
    https://drive.google.com/file/d/1uz7dlN3ec8fjEdJiMV_VecBU7oHzf6w5/view?usp=sharing 

    Thanks,
    If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

    • showy's avatar
      showy
      Helper II
      1 year ago

      Hi ajaybabuinturi this works very good and it the behaviour i wanted.

      I tried it now with multiple Items but now i am facing a strange topic. I added 2 more items and Entry in F_Sales.
      The switch between Price1 - Price 4 works and the values are correct but the total sum dont reacts to the selection. 

      Do u also have a solution for that?

       I also updated the dropbox link with the current data

      I think i have to use SUMX but i dont get it right....



      • ajaybabuinturi's avatar
        ajaybabuinturi
        Super User
        1 year ago

        showy could you please use beow updated measure to get correct Grand Total.

        _Measure(Updated)2 = 
Var SelectedColumn = SELECTEDVALUE('PriceFields'[PriceColumn])

Var Result = 
SUMX(D_Item, 
    Var Quantity = CALCULATE(SUM(F_Sales[Quantity]))
    Var SelectedPrice = SWITCH (SelectedColumn,
                        "Price1", D_Item[Price1],
                        "Price2", D_Item[Price2],
                        "Price3", D_Item[Price3],
                        "Price4", D_Item[Price4],
                        BLANK())
    Var Result = Quantity * SelectedPrice //Final Result1
    Var Result2 = IF(ISBLANK(SelectedPrice), Quantity, Quantity * SelectedPrice) //Final Result2
    RETURN
    Result2 //Based on business needs chose any one result
    )
    
RETURN
Result

         

        Thanks,
        If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.