Hey guys
First of all, i hope this is the right place to ask this.
My account id is something like this, in this order:
and i’d like to be able to select all account values From those accounts121 till 12111. I have to be able to choose or to write it.
You could always put your Account ID in a slicer, and just select those accounts, or do you want just those Account ID’s to be included in a specific measure? Please clarify what it is you are looking for.
Thanks
Jarrett
Hi @JarrettM
It sounds right doing that way, i mean, each filter box for each account. 2 filter box then.
Let’s suppose i need all account values from 111 till 131. The measure should show, the values for each account, 111, 1111, 11111. 121, 1211, 12111 and 131.
I would go with something like this:
SelID = IF ( LEFT( SELECTEDVALUE( table[Account ID] ), 3) = “121”, 1, 0 )
You can then apply that as a visual-level filter in the filter pane of whatever visual you create.
I hope this is helpful.
– Brian
PS if you have a set of complex filter conditions on Account ID, you can create a series of dummy (0/1) variables and set up the AND/OR conditions as described in this video:
Hey @BrianJ
Thanks for your answer and your video.
Actually, your video gave me an idea about not only what i want but to explain myself better.
First, don’t forget that the column AccountID is a text field.
What i would suggest (but don’t know how) is to build 2 parameters, accountID_from and accountID_Till from (i think) two list of accountid unique values.
Then, put them both in each slicer.
The measure would be something like,
CALCULATE ([Balance],
FILTER( Tab Gen Ledger,
Tab Gen Ledger [AccounID] >= [accounIT_from &&
Tab Gen Ledger [AccounID] <= [accounIT_till ))
Does this make some sense, in your opinion? Do you know how to do this or do you have another simpler way of doing this?
Thanks a lot Brian
Won’t be able to use >= or <=, because Account ID is Text field. You could do a measure something like this:
CALCULATE ([Balance],
FILTER( Tab Gen Ledger,
Tab Gen Ledger [AccounID] IN {“121”, “1211”, “12111”} ) )
This would give you the balance for the ID’s listed only.
Thanks
Jarrett
Hey @JarrettM
Yes, you’re right. The idea is to get all values between those 2 chosen accounts, on each filter box, accountID_from and accounID_till. Is there any other way?
@pedroccamara Account ID always contain only numbers or does it have text characters as well?
always numbers @AntrikshSharma
@pedroccamara Then why not convert them into numbers in Power Query?
Another option would be to change your Account ID to a “whole number”, and choose don’t summarize in the Summarization section. Then you would be able to use the >= and <= for your measures.
Thanks
Jarrett
Hey guys
Changing that accountID column to number, i believe it won’t work because, for example, in the perspective of hierarchy level, the accountID 111 is bigger than 1111 and i have up to 11 numbers for some accountID…
Let me suggest this: how about having an index column for the accountID? That way, if i chose 2 accounts, let’s say from 511 till 5891(index columns = 266 and 271) would it be easier to work with? Could we do a measure with this?
Exactly what I was working on – see solution below:
Convert Acct ID text to Decimal:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“Tce7DQAgCEDBXagtRPzOQtx/DfNMjFx37qKqkkRzlp3uaAnl9l5Y/aMtlPd3Y+OPzlC++D4=”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#“Account ID” = _t, Balance = _t]),
#“Duplicated Column” = Table.DuplicateColumn(Source, “Account ID”, “Account ID - Copy”),
#“Split Column by Position” = Table.SplitColumn(#“Duplicated Column”, “Account ID - Copy”, Splitter.SplitTextByPositions({0, 3}, false), {“Account ID - Copy.1”, “Account ID - Copy.2”}),
#“Merged Columns” = Table.CombineColumns(#“Split Column by Position”,{“Account ID - Copy.1”, “Account ID - Copy.2”},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),“Acct Dec”),
#“Changed Type” = Table.TransformColumnTypes(#“Merged Columns”,{{“Acct Dec”, type number}})
in
#“Changed Type”
Filtered Balance =
VAR Sel1 = SELECTEDVALUE( Disconn1[Account ID] )
VAR Sel2 = SELECTEDVALUE( Disconn2[Account ID] )
VAR Result =
CALCULATE(
[Total Balance],
FILTER(
ALL(Data),
Data[Acct Dec] >= LOOKUPVALUE( Data[Acct Dec], Data[Account ID], sel1 ) &&
Data[Acct Dec] <= LOOKUPVALUE( Data[Acct Dec], Data[Account ID], Sel2 )
)
)
RETURN
Result
Hello @BrianJ
I cannot open your file right now to check the solution but, accordingly to what i’m seeing on the picture, i believe the first filter for account ID is 111 and the second filter, the accountID_till is 11111. So, it should only show accounts starting with 11. That’s the purpose, unless i’ve picked 111 and 13111 which wasn’t the case. I’ll check it later anyway.
It’s weird, the measure is ok, but not the table…
because you are using two slicers on the same field, you need to do so using disconnected tables. Thus, to filter the table you need to add another measure like this in the filter pane:
Screen =
IF(
AND(
SELECTEDVALUE( Data[Acct Dec] ) >= [Harvest1],
SELECTEDVALUE( Data[Acct Dec] ) <= [Harvest2]
), 1, 0
)
In thinking about this further, the above solution may be an example of “just because you can, doesn’t mean you should”. All things considered, if I had to deal with this problem in one of my own reports, I would probably use a modified version of @JarrettM’s simpler suggested approach above - using PQ to extract the first two characters of the Account ID text string, and then using that as the upper level of a heirarchy slicer as below (and on page 3 of the attached revised solution):
I hope this is helpful. Full solution file attached below.
Very good @BrianJ
I’ve chosen your solution although Jarret’s seems also a simple, good and faster one.
Thanks all of you guys.
You’re the best
Pedro
Yes it was. I thought so too. 
I’ve done your way and Jarretts way. My client will decide.
I believe it’s very nice to come up with more than one solution. It means a lot i think.
Thanks again for everything