topic Re: Filter Using a Slicer With No Relational Table in DAX Commands and Tips

Filter Using a Slicer With No Relational Table

KleinhansStefan — Thu, 26 Oct 2023 05:55:09 GMT

Good day,
I am trying to filter my data like you would do in SSRS by using a paramter.
I want to see Site Visits to Clients base on a Slicer Value.
I have the Main Table with client, and a calculated column to calculate the site visits based on the Arrival time.
Then I created a new table with a calculated value for the numbers
" CountFilter = GENERATESERIES(1, 20, 1)"

Now I cant get it to filter
I have tried to create a relationship between the two by and it went for Many-to-One (MainTable - to - CountFilter)
When I leave it on single then I only get a 1 or 0 value, if I leave it on Both direction then i get all the values from 1 to 20 but it shows no data when I select anything other than 1

Visual is a simple table with customer and the Sum of Site visits
the DAX calculate column code is

SiteVisits = IF(NOT(ISBLANK('MainTable'[ArrivalTime].[Date])), 1, 0)

Does anyone have an Idea where I can start to troubleshoot. Or if there is any alternitave?
Main Goal is select 5 for instance and then i see all customers with 5 site vistis or more. Greater than 5

PS.
I tried this below as well, did not work.

Filtered Visits =
VAR SelectedCount = SELECTEDVALUE(CountFilter[Value])
RETURN
CALCULATE(
COUNTROWS(FILTER('MainTable', NOT(ISBLANK('MainTable'[ArrivalTime].[Date])))),
FILTER(CountFilter, CountFilter[Value] <= SelectedCount)
)

Re: Filter Using a Slicer With No Relational Table

ValtteriN — Thu, 26 Oct 2023 06:06:01 GMT

Hi,

Here is one way to do this:

Data:

Parameter:

VisitFilter = GENERATESERIES(0, 20, 1)

Visit measure =

Visit neasure = COUNT(Visits[Visit]) //using simple count for demo

For actual filtering use this kind of measure:

Filter measure = IF([Visit neasure]=[VisitFilter Value],1,0)

Now place it as a filter like this:

After selecting a value the filter displays the correct value:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Filter Using a Slicer With No Relational Table

KleinhansStefan — Thu, 26 Oct 2023 06:47:04 GMT

ValtteriN
Thank you for this. I eventually got it to work, had some trouble getting to where you where.
So the concept works, I was just wondering is there was no way to show customer greater than selected value?

Re: Filter Using a Slicer With No Relational Table

KleinhansStefan — Thu, 26 Oct 2023 06:47:58 GMT

ValtteriN
Never mind, umm I found my issue while I was replying.

Filter = IF([SiteVisit] >= VisitFilter[VisitFilter Value], 1, 0)

Thanks for your help