Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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);"")
Solved! Go to 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
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."
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:
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
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |