Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rhl94
Advocate III
Advocate III

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

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

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 

@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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.