HasOneFilter in the total

[Anonymous]

Hi,

I have a table visual with two columns: customer_name, transactions_count. Transactions_count is a measure that calculates the number of transactions in a table associated with customers.

Apart from that, I need to have the total row in this visual, but the transactions_count measure has to be calculated differently for the total. I am utilizing the HasOneFilter function to check wheather it's the total row or not:

IF(HASONEFILTER(Customer[Name]), 1, 0).

It's working fine unless I apply the slicer-filter on the customer name: if I choose more than 1 customers in the slicer, the calculation seems to be working properly: it shows 1 for each table record, and shows 0 for the total record.

When I select only one customer in the slicer, the HasOneFilter function shows 1 for the Total record too. In this case I cannot differentiate a table record from the total record, and the table visual shows incorrect calculation for the total.

Is there any method to circumvent this issue, please?

[Anonymous]

I can't see any need to differentiate between an individual row and the total in case only one customer is visible in the current context since in this case the total IS the individual row. What it also means is that you cannot make a distinction between a total row and an individual row---they are both the same, they see the same data.

[Anonymous]

Thanks for answering! 

It probably makes sense in most cases, mine is different 🙂

My report consumer wants to see the customer's transactions as one row, i.e. concatenating them, like "o87ji78, 98hjl576, 2134hjk;". At the same time they want to see their count in the total. I calculate the concatenantion as a measure, and it works. Now I would need to create another measure calculating count and show it in the Total row. Thus I need to be able to differentiate between a detail row and the total row.

It may look like this:

customer_name|transactions
customer1|098oij98; ioj98kj; 231gu231;
customer2|7812hjkl; hu341;
total|5

Sorry if I confused you with the names and terms I used in the initial question, I just tried to make things simplier and wrote "transaction_count" instead of "concatenantion"..

[AlB]

Hi @Anonymous 

How about you create an additional one-column table with the customer names, let's call it CustomerAux and create a 1-to-many relationship with your current Customer table. You leave Customer[Name] in the visual but use CustomerAux[Name] in the slicer.

HASONEFILTER(Customer[Name]) 

will only be true at the non-total rows then

Doing it as you do now the filter context from the rows in the visual and that from the slicer will be interfering

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Thanks a lot for the suggestion!

Unfortunately I am not allowed to modify the current report schema 😞

Do you think the issue could be resolved without any model changes?

Also I have created a small model illustrating the problem just in case.

I have a Client table:

create table Client(id integer not null, client_name varchar(100))
insert into Client values (1, 'Client_1'), (2, 'Client_2'), (3, 'Client_3');

Then I import that table into a pbix file.
In that file I create a measure with the below definition:
IsTotal = If(HASONEFILTER(Client[client_name]), 1, 0)

Then I create a slicer on the client_name. It shows 3 records.
Then I create a table showing the client_name, and the IsTotal measure.

Then I start to play with filter. As one can see, when I have more than one client_name selected, the total shows 0, which is fine as now I can differentiate a table row from the total row. But when I have only one client selected in the slicer, the total shows 1, and thus I cannot figure out anymore if its a table or total row.
Please, see the screenshot are attached.

[amitchandak]

@Anonymous , where are the two measures you want to use one for line level and one for total level. This is going set the total to 0  whenever there is more than one customer.

Something like this

IF(HASONEFILTER(Customer[Name]), [measure], [total measure])

[Anonymous]

Thanks for answering.

I am sorry if I was not clear with my question. Are you asking for the defenition of the measure I am using? I do not have it right now on hand. What I remember is it's quite complicated, and the calculation has to depend on whether it's a table row or the total row. 

In other words:

client_name --- #_of_transactions

------------------------------------

Client1 --- 5

Client2 --- 7

Client3 --- 4

------------------------------------

Total --- 8

So I have to calculate # of transactions differently when it's the total row. In order to do that I am using HasOneFilter function to check if it's a table or total row. If the table has more than one client_name (more than one row), then HasOneFilter can help me to differentiate between scope of the rows. But when I filter client_name down to 1 value (I have 10 clients, but in the slicer I check one client only), then HasOneFilter shows True in the Total. This is not what I would want and thus I cannot apply different calculation for the Total value as I cannot determine if it's the Total row.

So I am wondering if there is a way to determine the total row even if I have filtered the client dataset down to one row only. Hope it makes sense now 🙂