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

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 

    • zoloturu's avatar
      zoloturu
      Memorable Member
      6 years ago

      rhl94 ,

       

      For testing purpose, write two measures in SSAS:

      MeasureTest = HASONEVALUE ( Company[name] )

MeasureTest2 = HASONEFILTER ( Company[name] )

      Then deploy it to a server of course.

      After select a single company at slicer, you should receive TRUE in both measures.

      Then remove filters and both should be FALSE.

       

      Post the results here.


      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

        Works as expected.

         

        Picked a blank for anonymous reasons