Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

SelectedValue for dynamic Measure

  • 1.  SelectedValue for dynamic Measure

    Posted Sep 10, 2021 05:35 AM
    Edited by H Z Sep 10, 2021 06:22 AM
    Hi all ,

    Would like to seek some help doesn't seem to able to get it work.

    SWITCH(TRUE(),SELECTEDVALUE(Parameter[Parameter])>=CALCULATE(Count('TotalSales'),FILTER('Sales Year]=SELECTEDVALUE(Parameter[Parameter]))) ,"Promotional Goods","Others Misc")

    Basically the parameter is a selection of different items , seems like the Calculate part works however trying to assign whether to a promotional good / misc is not working any idea.
    Trying to create a dynamic measure. Somehow aggregation of both without sub lvl grouping is not working


  • 2.  RE: SelectedValue for dynamic Measure

    Top Contributor
    Posted Sep 14, 2021 10:51 AM
    Hi @H Z,
    The formula you copied contains errors after the FILTER function, It's look like there is no distinction between the table and the expression.
    Consider debugging your code using variables for more readability.

    ------------------------------
    If any discussion or answer help, please mark it as "Recommend"
    ------------------------------

    Best regards
    Mehdi HAMMADI

    MCT | MCSA BI Reporting | MCSE Data Management and Analytics | Data Analyst Associate
    2019 Ruby Award Dynamic Communities
    ------------------------------



  • 3.  RE: SelectedValue for dynamic Measure

    Posted 29 days ago
      |   view attached

    Hi @Mehdi HAMMADI,

    Seems am still stuck trying to debug .
    Wondering could you advise further ​basically the assignment to Individual ID no issue . how am I able to achieve the aggregate version.



    ------------------------------
    H Z
    Manager
    Kuala Lumpur
    ------------------------------

    Attachment(s)

    pbix
    Test.pbix   59 KB 1 version


  • 4.  RE: SelectedValue for dynamic Measure

    Top Contributor
    Posted 29 days ago
    Edited by Mehdi HAMMADI 29 days ago

    Can you explain what are you trying to achieve with a numeric exemple;
    Is it about What-If parameters and are you trying to compare the total sales to the slected value of the parameter and return Under Sales or Exceed Sales.
    Actually when selecting 50000 in the parameter column The measure return Exceed Sales if Total_Sales <= 50000 and under sales if it is greater than 50000 

    ------------------------------
    If any discussion or answer help, please mark it as "Recommend"
    ------------------------------

    Best regards
    Mehdi HAMMADI

    MCT | MCSA BI Reporting | MCSE Data Management and Analytics | Data Analyst Associate
    2019 Ruby Award Dynamic Communities
    ------------------------------



  • 5.  RE: SelectedValue for dynamic Measure

    Posted 29 days ago

    Hi @Mehdi HAMMADI,

    The if else will be just a mock up , what i am trying to achieve is on dynamically measure to respective reflect using the matrix .
    Eg .

    Final Output if i select parameter of 50k

    Under Sale  - 5

    Exceed Sales - 25 

    ​Eg 2

    Final Output if i select parameter of 100k

    Under Sale  - 1

    Exceed Sales - 29

    If I list it by ID the measure are working fine however without the ID seems is not giving me the desire output. 



    ------------------------------
    H Z
    Manager
    Kuala Lumpur
    ------------------------------



  • 6.  RE: SelectedValue for dynamic Measure

    Top Contributor
    Posted 29 days ago
    Edited by Mehdi HAMMADI 29 days ago
    HI @H Z,
    Can you please try this measure
    New Measure 3 =
    VAR _Target = SELECTEDVALUE('Parameter'[Parameter])
    VAR _TotalSales = SUM('2018'[Sales]) -- change this line if you need to change the filter context
    RETURN
        IF(
            HASONEVALUE('2018'[ID] ) , -- to hide the result for grand total
            SWITCH( TRUE() ,
                _TotalSales <= _Target , "Exceed Sales"  ,
                "Under Target"
            )
        )


    I have updated the measure and you measure is working fine you just have to check the filter context

    ------------------------------
    If any discussion or answer help, please mark it as "Recommend"
    ------------------------------

    Best regards
    Mehdi HAMMADI

    MCT | MCSA BI Reporting | MCSE Data Management and Analytics | Data Analyst Associate
    2019 Ruby Award Dynamic Communities
    ------------------------------



  • 7.  RE: SelectedValue for dynamic Measure

    Posted 29 days ago
    Hi @Mehdi HAMMADI,

    Tried the new measure , seems its still functioning like my measure  ​. Not on the aggregate output basis



    ------------------------------
    H Z
    Manager
    Kuala Lumpur
    ------------------------------



  • 8.  RE: SelectedValue for dynamic Measure

    Top Contributor
    Posted 29 days ago
    Edited by Mehdi HAMMADI 29 days ago
    Maybe this video from Enterprise DNA can help​ you understand what is wrong in the way you are writing the formula The Hidden DAX Trap In Power BI - YouTube
    Furthermore, the only context (aggregation level) you provided in the data sample is the Sales ID.
    Are trying to show the result also on the grand total ?

    ------------------------------
    If any discussion or answer help, please mark it as "Recommend"
    ------------------------------

    Best regards
    Mehdi HAMMADI

    MCT | MCSA BI Reporting | MCSE Data Management and Analytics | Data Analyst Associate
    2019 Ruby Award Dynamic Communities
    ------------------------------