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

Count / countrows of column in fact-table combined with search slicer

Dear Community!

 

I have a challenge I do not seem to be able to solve alone.

 

In my fact-table, one column has all the names of salesagents. It can be one agent only, or several. If more than one, the value is delimited by ";".

 

To be able to slice based on each salesperson, I have set up an unrelated table, that contains all salesagents, one row per agent.

 

I use this unrelated table in a slicer, combined with a SEARCH-function, I check whether the agent can be found in the fact table column. If the search-measure to filter the visualizations and it works well.

 

Problem: If I try to count the rows matching the search, and putting it in a card, it fails.

 

My search-function is set ut like this:


mAgent_Search = SEARCH(
SELECTEDVALUE(UnrelatedTable[AgentName]),
SELECTEDVALUE(FactTable[AgentNames]),,
-1)




Toalme_0-1618576507493.png

I would need a card that counts the number of rows that is in the fact table, filtered for all 'normal' slicers AND the slicer that is search-based on the UnRelatedTable's AgentName.

 

Is there a way to do this? Very much appreciate any help.

 

 

Best regards

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@Toalme , Try a measure like


New measure =
countx( filter(, SEARCH( SELECTEDVALUE(UnrelatedTable[AgentName]),FactTable[AgentNames],,0) >0), FactTable[AgentNames])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
daxer
Solution Sage
Solution Sage

@Toalme 

 

First of all, your model is incorrect. Storing in a field more than one value is a NO-NO. Don't do it unless you want to suffer and have horrible performance problems. The correct way to model this is to have a bridge table that will join to your fact table and to your dimension of Agents. Such a bridge would be hidden, of course. It would store the associations between your fact row and each relevant Agent. Once you've got the right setup, you can start writing DAX. It'll be easy then.

 

 

// To get the count of rows in the FactTable
// all you have to do in the correct model
// is to write:

[Count of Rows] = countrows( FactTable )

 

 

The structure of the model should be: [Agents] 1 -> * [AgentSaleBridge] * <->1 FactTable. The filtering between FactTable and the bridge must be set as two-way (<->).

 

Don't fall into the trap that so many do. They think that a bad model will do. No, it won't. Sooner or later they will have huuuuuuge problems and will have to re-work the model anyway. But it'll be very painful if you've started on the wrong foot in the beginning.

amitchandak
Super User IV
Super User IV

@Toalme , Try a measure like


New measure =
countx( filter(, SEARCH( SELECTEDVALUE(UnrelatedTable[AgentName]),FactTable[AgentNames],,0) >0), FactTable[AgentNames])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

@amitchandak @daxer 
Thank you so much for your replies; I got it to work just the way I hoped for, using the measure you suggested, @amitchandak ! Thanks!



@daxer , I am aware of the weaknesses of the model, this is the way Microsoft Planner exports look like. Out of curioursity; how would I build the bridge table? Manually, or is there a way to split delimited in one row / column, into separate rows, row inserted for each delimited value? Can this be done in DAX or Transform data maybe? Would like to avoid building this manually, in case new names are added.

 

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 Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors