Power BI Count IF Like

[Anonymous]

I have a spreadsheet which gets updated weekly and then have a dashboard in PowerBI. I want to be able to Query coloum 3 for all names like DA_ and then count coloum 4 for enabled/disabled

Is this possible?

[ZunzunUOC]

Hi @Anonymous , Could you share your origin data?

Thanks.

[Anonymous]

Thats what the excel is like?

[ZunzunUOC]

Ok, you can create a calculated column like this:

enable = IF(LEFT(Like[SAMACCOUNT];3)="DA_";"TRUE";"FALSE")

I recognize what you mean with count.

Best Regards,
Miguel

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Getting the following error. Even if i change SAMACCOUNT in the query to Column3 it gives the same error?

[ZunzunUOC]

"like" is your table name?

[Anonymous]

The table in this instance is called "test" but even that does not work?

[ZunzunUOC]

Ok, I see two wrong things:

1. Names. You must change my names for your names (table and columns).
2. In your capture, your columns has no names. You must define the first row like a header.

Otherwise, I test the code and works fine. Give me your feedback and see it.

[Anonymous]

Ok so i have named the coloums and the table? and it is still the same error? could you show your example?

[ZunzunUOC]

Could you change ";" per ","?

[Anonymous]

So that seems to of fixed the ; issue. However it doesn't seem to allow me to count the enabled accounts?

[ZunzunUOC]

Have you create a calculated column?

[Anonymous]

Ok i see now it works. However, that is not what i was looking for? I wanted to be able to add a measure so i can show it on a dashboard. I want to be able to Count all accounts which start with DA_ and in the enabled coloum are True. so if there are 30 accounts and only 10 are enabled it will show 10 not 30.

[ZunzunUOC]

Ok, try with this:

MeasureCount = CALCULATE(COUNTROWS(Like);FILTER(Like;Like[enable]="TRUE"))

[Anonymous]

But i only want to count it if it has DA_ in SamAccount?

[ZunzunUOC]

Then, change the code to:

MeasureCount = CALCULATE(COUNTROWS(Like);FILTER(Like;LEFT(Like[SamAccount];3)="DA_"))

[Anonymous]

But that doesnt do both so i need to count the enabled accounts that match DA_?

[ZunzunUOC · Accepted solution]

MeasureCount = CALCULATE(COUNTROWS(Like);FILTER(Like;LEFT(Like[SamAccount];3)="DA_" && Like[enable]="TRUE"))

Best Regards,
Miguel

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you 🙂 

[v-chuncz-msft]

@Anonymous 

By the way, you may help accept the solution above. Your contribution is highly appreciated.