cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BPrivitera
Frequent Visitor

Combine filter results into one visualisation

Fellow Power BI'ers,

 

Our users have a requirement for a lot of ad hoc type reporting.  This will be of the type ...

Retrieve Sales data where FIELD_A = 'ABC' AND FIELD_B = 'XYZ'

 

There is no (or usually none) relationship between FIELD_A and FIELD_B.  Results are usually mutuall exclusive.

 

It's like ... give me all sales for Persons over 50 years of age  AND  for Persons who ride a skateboard (Usually mutually exclusive).

 

We can get the users to write measures for each condition which look like this  (need to wrap a MIN() around the fields as we  source from an Analysis Services database (Tabular) with a live connection and don't have access to data fields that aren't measures)  ...  

 

M_Incl_10 = IF(LEFT(MIN(Trans[Promo]),3) = "G81" || LEFT(MIN(Trans[CtrlID]),2) = "F9" , 1, 0)

 

Which mostly works, however again, since we source from SSAS, the data isn't always accurate (not sure why but it depends on what fields we choose in the visualisation).

 

Yes, we could write a bunch of measures in the Tabular Model, however this requires interaction from the BI team as the users don't have access to the model itself (and nor do we want to give it),

 

As such, is there any other way to achieve this goal in Power BI against a SSAS Tabular Model live connection where we could empower the users to perform these ad hoc queries themselves.

 

Any help very much appreciated.

 

Regards

Basil

5 REPLIES 5
TomMartens
Super User II
Super User II

Hey Basil,

I'm wondering if you already have looked at the feature called "Cross Highlight", it has been released with Power BI Desktop in February, and it's not a feature that one can turn on and off, but it's more like a "behavior".

 

You will find a description in the video for the February 2018 release:

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-february-2018-feature-summary/#crosshighli...

 

This feature may not supported by all custom visuals until now.

 

Regards,
Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Tom,

 

At first glance, it certainly looks promising.

 

I'll check it and and get back to you.  Thanks for the quick reply.

 

Cheers

Basil

Tom,

Thanks again for your reply.

Mmmm, not quite.  Still suffers the same issues as the previous methods I've tried (though will be good for another couple of scenarios we have), It still depends on what fields you choose in the visualisation (usually just a table) as to results (records) are actually shown.

Regards

Basil

hi, @BPrivitera

     You may try to use dynamic row level security with Analysis services tabular model,

So the user doesn't have to write a measure to filter the data.

Reference:https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-row-level-security-onprem-ssas-tabular

https://docs.microsoft.com/en-us/power-bi/desktop-rls

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Lin,

Thanks for replying.

Unfortunately, the filtering that needs to happen is quite Ad Hoc so I don't think implementing RLS will be my answer as this will start to affect other reports that they should be able to run.

I'll keep looking for an answer but perhaps PowerBI is not the answer for this one.

Regards

Basil

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

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.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - July 22, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors