Forum Discussion

rhl94's avatar
rhl94
Advocate III
6 years ago
Solved

Create benchmark for specific categories

Hi

 

I am looking to create a benchmark number that is based on region. My measure looks like this and works perfectly when a single company name is selected.

 

Region bench = 
     VAR Region = LOOKUPVALUE(Company[dss_employee_count];Company[name];VALUES(Company[name]))
RETURN
     IF(HASONEVALUE(Company[name]);CALCULATE([Skadeskroner];ALL(Company);Company[dss_employee_count]=Region);"")

 

The issue is that the whole visual is broken when a company name is not selected (e.g. the LOOKUPVALUE() returns multiple values which is not allowed. I want it to return blank if there are no company selected. I've tried to put it into an IF statement, however it does not solve it. Any workaround or better way to execute a benchmark measure?
 
At first I wanted to solve this using SELECTEDVALUE(), however it is not available in either the Power BI Desktop or the SSAS tabular model (I think it is some kind of restriction, since the function is available when I open a blank Power BI file. 
Need Help
  • zoloturu's avatar
    zoloturu
    6 years ago

    Give a try with this one

    Region bench =
VAR __company =
    VALUES ( Company[name] )
VAR __region =
    IF (
        HASONEVALUE ( Company[name] );
        CALCULATE (
            SUM ( Company[dss_employee_count] );
            FILTER ( ALL ( Company[name] ); Company[name] = __company )
        );
        BLANK ()
    )
RETURN
    CALCULATE (
        [Skadeskroner];
        FILTER ( ALL ( Company ); Company[dss_employee_count] = __region )
    )

    Regards,
    Ruslan Zolotukhin (zoloturu)
    BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
    -------------------------------------------------------------------
    Did I answer your question? Mark my post as a solution!
    It was useful? Press Thumbs Up!

    You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups

7 Replies

  • zoloturu's avatar
    zoloturu
    Memorable Member
    6 years ago

    Hi rhl94 ,

     

    SELECTEDVALUE function works fine in Power BI Desktop. So it should work for you too.

     

    Regarding your measure, what is the error message at a visual?


    Regards,
    Ruslan Zolotukhin (zoloturu)
    BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
    -------------------------------------------------------------------
    Did I answer your question? Mark my post as a solution!
    It was useful? Press Thumbs Up!

    You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups

    • rhl94's avatar
      rhl94
      Advocate III
      6 years ago

      Hi zoloturu 

       

      SELECTEDVALUE is not supported in our version of SSAS, as it was introduced after ;

       

      "SELECTEDVALUE function was introduce to PowerBI in July 2017, after the release of SQL Server 2016. It can not be implemented on Microsoft SQL Server 2016 Analysis Services."

      Source: https://social.msdn.microsoft.com/Forums/en-US/c4cd8dcb-87e8-45b0-b116-5016e28f973d/dax-compatibility-error-selectedvalue-is-not-a-valid-function?forum=sqlanalysisservices

       

      I have tried using 

      IF ( HASONEVALUE ( Table[column] ), VALUES ( Table[column] ) )  

      but it still evaluates and this returns an error;

      It does seem to return blank however, when I remove "name" from the table:

       

      • zoloturu's avatar
        zoloturu
        Memorable Member
        6 years ago

        Hi rhl94 ,

         

        Try this one:

        Region bench =
VAR Region =
    IF (
        HASONEVALUE ( Company[name] );
        LOOKUPVALUE (
            Company[dss_employee_count];
            Company[name]; VALUES ( Company[name] )
        );
        ""
    )

RETURN
    CALCULATE (
        [Skadeskroner];
        ALL ( Company );
        Company[dss_employee_count] = Region
    )

        Regards,
        Ruslan Zolotukhin (zoloturu)
        BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
        -------------------------------------------------------------------
        Did I answer your question? Mark my post as a solution!
        It was useful? Press Thumbs Up!

        You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups