cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rhl94
Helper II
Helper II

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. 
1 ACCEPTED SOLUTION
zoloturu
Memorable Member
Memorable Member

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

View solution in original post

7 REPLIES 7
zoloturu
Memorable Member
Memorable Member

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

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

 

I have tried using 

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

but it still evaluates and this returns an error;

err.png

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

 

err.png

zoloturu
Memorable Member
Memorable Member

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

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
Memorable Member
Memorable Member

@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

 

Works as expected.

 

Picked a blank for anonymous reasons

 

err.png

zoloturu
Memorable Member
Memorable Member

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

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors