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 =...
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

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

      • rhl94's avatar
        rhl94
        Advocate III
        6 years ago

        Hi zoloturu 

         

        I've tried to wrap an if statement but with no luck. Returns same error as previous image. It seems like it somehow still calculates the