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

[Anonymous]

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)

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

[Anonymous]

@Anonymous 

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 · Accepted solution]

@Anonymous , Try a measure like

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

[Anonymous]

@amitchandak @Anonymous 
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!



@Anonymous , 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.