Forum Discussion
Helper IIGet Account Numbers which starts with specific numbers
Hi Deeksha,
So, you want to filter your visuals with the account numbers starting with specific digits?
In that case you might want to create a calculated column with the the formula:
IF(LEFT(Sheet2[Account],1) in {"3","4","6"}, 1)and then use this column in FiltersYou may refer to the sample pbix file hereLet me know if this is what you are looking for.Regards,VivekIf this reply has answered your question or solved your issue, please mark this question as answered. Answered questions help users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!deekshaI am using the new column (Filtered Account in the sample pbix file I shared) as a support field to filter my visuals. I am not using Filtered Account column in any visuals, but to filter the visuals using the Filter pane
I have created two visuals for your reference. Both the table has the same fields:
The left table shows all the accounts (with a total of 488) and the right table shows account number starting with 3,4 & 6 (with a total of 252).
How it has been achieved is through using the Calculated Column: Filter Account in the Filter Pane:
Filter Pane for the left table:
Filter pane for the right table:
This is giving me the desired results.
I hope this is what you are looking for.
Community Champion
Hi,
You can try this:
Sum selected = CALCULATE(SUM(Sheet2[Sum]),FILTER(Sheet2,LEFT(Sheet2[Account],1) in {"3","4","6"}))
or
Sum Sel 2 = SUMX(Sheet2,IF(LEFT(Sheet2[Account],1) in {"3","4","6"},Sheet2[Sum]))
Regards,
Vivek
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions help users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
Helper IIHi,
Thank you for the reply, I tried your formula but it is giving any other value as you can see in the picture, I want it to filter the accounts and only display the accounts which start with 3 or 6 but it is calculating something else.
- vivran22
Hi Deeksha,
So, you want to filter your visuals with the account numbers starting with specific digits?
In that case you might want to create a calculated column with the the formula:
IF(LEFT(Sheet2[Account],1) in {"3","4","6"}, 1)and then use this column in FiltersYou may refer to the sample pbix file hereLet me know if this is what you are looking for.Regards,VivekIf this reply has answered your question or solved your issue, please mark this question as answered. Answered questions help users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!- deeksha
Hi, Thanks for the reply, I tried the new formula but again it is showing something else. it should display only the accounts which are starting with 3,4 and 6 but it is showing some other values.
- vivran22
So you created the calculated column:
and then applied the filter on the visual:
still not getting the desired results?